Resolved: Can't understand what LEFT JOIN does
Good morning. The resources say that "left joins can deliver a list with all records from the left table that
do not match any rows from the right table". So, does t1 LEFT JOIN t2 show the records of t1 that are not in t2? And if so, if t1 has 11 rows and t2 has 26 rows, shouldn't t1 LEFT JOIN t2 have at maximum 11 rows?
Instead, if t1 LEFT JOIN t2 shows the records in t1 that are not in t2 + the records they share, isn't this like showing all table t1? Thank you
Hi Alessandro!
Thanks for reaching out.
Yes, your logic is correct. When using LEFT JOIN the order of the tables matters (when using INNER JOIN=JOIN the order does not matter!). So, every record, that is contained in the left table, (the first one) and is not contained in the right table, (the second one) will be included in the result set.
Hope this helps.
Best,
Tsvetelin
So if t1 has 11 rows and t2 has 26 rows, shouldn't t1 LEFT JOIN t2 have at maximum 11 rows?
Hi Alessandro!
Thanks for reaching out.
The result set will contain 11 rows only if these rows are not contained in the t2 table. If some of these rows are contained in the t2 table, then the result set will contain 11-n rows where n is the number of rows contained in both tables.
Hope this helps.
Best,
Tsvetelin
Good morning. It was exactly how I thought it worked, so why the following code returns 26 rows and not at maximum 11?
SELECT
d.dept_no, m.emp_no, d.dept_name
FROM
departments_dup d
LEFT JOIN
dept_manager_dup m ON m.dept_no = d.dept_no
ORDER BY m.dept_no;
departments_dup contains only 11 rows; dept_manager_dup contains 26 rows. If it works as you said, I expect at maximum 11 rows, but it gives me 24 rows. Why? Thanks
Hello Allesandro,
I think the reason for the result having more than 11 rows is that the columns ON m.dept_no = d.dept_no is not a one-to-one match, it is one-to-many.
Take the dept_no 'd009' for example: there are four records in the dept_manager_dup table with 'd009' as their dept_no, but all of them satisfy the match ON m.dept_no = d.dept_no. Considering all these match cases, we already have 20 rows in total. In terms of the Venn diagram, we only have the intersection of the two tables (with respect to dept_no).
Now, since we are using LEFT JOIN, we must also add all dept_no in departments_dup table to the result, which are 'd001', 'd010', 'd011', and Null (Public Relations). In the Venn diagram, these four records fill the region below. Then we add all the records from that region, hence the 24 records.
Maybe the logic explained above only holds true when the reference columns for ON are uniquely matched.
Ok thank you. So I think that if t1 has r1 rows and t2 has r2 rows, the maximum number of rows of t1 left join t2 is r1 * r2, isn't it? But I can't understand one thing: the resources say that "left joins can deliver a list with all records from the left table that do not match any rows from the right table". But you said that we must add either this (second image) or the common rows of the two tables (first image). So what of the two things is the result of t1 left join t2? First image + second image or just second image? Thanks
Hello Alessandro,
The result of t1 left join t2 would the the sum of the records found in each image. In terms of the resulting table in the lecture, the records below fall within the second image. These are records from t1 that do not match any rows from t2.
And here are the records where there is a (one-to-many) match in m.dept_no = d.dept_no.
When it comes to the maximum number of rows, I am not certain if it is possible to reach r1 * r2 records with left join, as that number of records are attainable with Cross join only (as far as I know).
Hope this helps.
Ok so the resources are just confusing with the statement "left joins can deliver a list with all records from the left table that do not match any rows from the right table", it's simply incomplete. Thank you very much!
Agreed with Alessandro Imbrìaco. The left join explanation in this recording is simply incorrect.
The key part of the statement "left joins can deliver a list with all records from the left table that do not match any rows from the right table" is the 'can'.
While left joins can do the above, it does not mean that that's only what they do when they are used.
In the "Left Join - Part II" lecture, the left join by default returns 1) the instersection of the two tables + 2)the rows that belong to the left table only.
To extract out only 2) the WHERE clause comes into play which happens to be satisfied when the rightmost column in SELECT (d.dept_name) is NULL -> It is this additional filtering with WHERE that achieves the extraction of 2) from the left join in this particular example.