CODEWITHSUNDEEP
sql
ekolis
ekolis

Reputation: 6796

Is it true that all joins following a left join in a SQL query must also be left joins? Why or why not?

I remember this rule of thumb from back in college that if you put a left join in a SQL query, then all subsequent joins in that query must also be left joins instead of inner joins, or else you'll get unexpected results. But I don't remember what those results are, so I'm wondering if maybe I'm misremembering something. Anyone able to back me up on this or refute it? Thanks! :)

For instance:

select * from customer
left join ledger on customer.id= ledger.customerid
inner join order on ledger.orderid = order.id -- this inner join might be bad mojo

Upvotes: 2

Views: 1220

Answers (2)

Royzipuff
Royzipuff

Reputation: 507

You remember correctly some parts of it!

The thing is, when you chain join tables like this

select * from customer
left join ledger on customer.id= ledger.customerid
inner join order on ledger.orderid = order.id

The JOIN is executed sequentialy, so when customer left join ledger happens, you are making sure all joined keys from customer return (because it's a left join! and you placed customers to the left).

Next,

The results of the former JOIN are joined with order (using inner join), forcing the "the first join keys" to match (1 to 1) with the keys from order so you will end up only with records that were matched in order table as well

Bad mojo? it really depends on what you are trying to accomplish.
If you want to guarantee all records from customers return, you should keep "left joining" to it.

You can, however, make this a little more intuitive to understand (not necessarily a better way of writing SQL!) by writing:

    SELECT * FROM 

    (
      (SELECT * from customer) c

    LEFT JOIN

     (SELECT * from ledger) l

    ON
       c.id= l.customerid
    ) c_and_l

    INNER JOIN (OR PERHAPS LEFT JOIN)

(SELECT * FROM order) as o

    ON c_and_l.orderid (better use c_and_l.id as you want to refer to customerid from customers table) = o.id

So now you understand that c_and_l is created first, and then joined to order (you can imagine it as 2 tables are joining again)

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270301

Not that they have to be. They should be (or perhaps a full join at the end). It is a safer way to write queries and express logic.

Your query is:

select *
from customer c left join
     ledger l
     on c.id = l.customerid inner join
     order o 
     on l.orderid = o.id

The left join says "keep all customers, even if there is no matching record in ledger. The second says, "I have to have a matching ledger record". So, the inner join converts the first to an inner join.

Because you presumably want all customers, regardless of whether there is a match in the other two tables, you would use a left join:

select *
from customer c left join
     ledger l
     on c.id = l.customerid left join
     order o 
     on l.orderid = o.id

Upvotes: 4

Related Questions