inner join and left join, how do these 2 statements work when they have inverted statements?

Question

Learning about joins and I understand the difference in what inner joins and left joins do, returns rows when there is a match in both tables, and left join returns all rows from the left table, even if there are no matches in the right table. I get that.

however in this example i am following along with, how they make the join confuses me:

select * from person join car on person.car_id = car.car_id;

gives me every person in the db that has a car_id value that isnt null.

select * from person left join car on car.car_id = person.car_id;

gives me every person in the db, regardless including those with a null car_id value;

what I dont get, are why these two statements are identical (apart from the left join) but these two values are inversed:

inner join: car on person.car_id = car.car_id;

left join: car on car.car_id = person.car_id;

can someone offer me a clear explanation as to why these statements are swapped about in the inner and left joins?

The join condition describes how to decide which rows match. The behavior of outer joins is about treating rows that fail to match. Two different things. Part of the confusion comes from old, nonstandardized syntax like `a *= b` where the order did matter. — shawnt00, Nov 26 '20 at 23:47
The old syntax was potentially ambiguous and didn't allow for compound/complication outer join conditions either. From that standpoint it wouldn't make sense for the order of comparison to matter. — shawnt00, Nov 26 '20 at 23:52
This is not clear. "gives me ..." may (sloppily) describe what you get, but it doesn't clearly tell us exactly what you got, or for what input. Same lack of clarity for your earlier statements about what the joins "do". Then it's not clear what you mean by "these two statements are identical" or "two values are inversed". (And "inversed" isn't a word.) Nor is it clear what you are talking about when you ask "why these statements are swapped about"--where did you see these statements & what was said about the change? PS This is an example of why code questions can be closed for lacking a [mre]. — philipxy, Nov 27 '20 at 04:54
When you don't get what you expect, say what you expected & say why you expected it, with justification referencing authoritative documentation. Otherwise the reason "why" is just that's how things are defined, per the documentation, and you're just asking for documentation to be rewritten, when we don't know what you misunderstood/misinterpreted, and we can't address your misconceptions. This includes whenever (actually, before you get the urge to) ask a question about debugging. — philipxy, Nov 27 '20 at 05:07

score 0 · Accepted Answer · answered Nov 26 '20 at 23:07

You do understand the difference between an inner join and a left join, so the question really is about the ordering of operands in an equality operation. Equality is a commutative operation: the ordering of operands does not matter: you can invert the operand without any impact on the result.

In other words, these two conditions are absolutely equivalent:

person.car_id = car.car_id
car.car_id = person.car_id

Unrelated side notes: the using syntax shortens the join condition when the columns being joined go by the same name (and disambiguates the column name(s) in the resultset). So:

select * 
from person 
left join car using(car_id)

Other unrelated good practices:

table aliases make the query easier to write and read
enumerating the columns in the select clause is better than select * (typically: it makes the query explicit about the structured of the returned dataset, gives you a chance to alias columns)

oh dear, I feel quite foolish now....I took the example as correct without out switching around the statements to check...you're right the ordering does NOT matter and the statements are indeed equivalent.... select * from person left join car on person.car_id = car.car_id; is indeed the same as select * from person left join car on car.car_id = person.car_id; i feel quite foolish but a valuable lesson has been learned here! and thank you for advice on good practices! I shall investigate those immediately! — dros, Nov 26 '20 at 23:24

inner join and left join, how do these 2 statements work when they have inverted statements?

1 Answers1