On 2015-07-27 05:48 PM, Marc L. Allen wrote: > When would that specific LEFT JOIN ever do anything except return NULLs for > the right table? It only accepts rows from work where fpath is null, and > only joins those rows where fpath = home.fpath. Since fpath must be null, > home.fpath should never be equal.
TLDR: Just a quick simple explain of the left joining phenomenon in case anyone needed it (Sometimes the basics elude me), feel free to skip this post if you know how it works - thanks. Left join is an outer join, meaning simply it iterates all rows in the joined table and adds all positive matches, duplicating as needed but never culling any rows from the originating tuple set (the LEFT table of the Join). So if I ask an SQL engine (any of them) to list me the numbers from 1 to 10 and LEFT JOIN onto that (ON) the word 'Odd' for numbers that are odd, it won't remove any of the original listing, it will simply add the fields to the lines where matches exist, and add NULL fields where no matches are found. A normal (Inner) join will only show positive matches found in both tables. Some SQLite-flavoured SQL to point out the above: (Note: this math works in SQLite because of how INT division is handled, it may not work exactly like this in other engines, but the idea is universal) CREATE TABLE Nums(x INT); CREATE TABLE Kinds(r INT, k TEXT); WITH CN(x) AS ( SELECT 1 UNION ALL SELECT x+1 FROM CN LIMIT 10 ) INSERT INTO Nums SELECT x from CN; INSERT INTO Kinds VALUES(1,'Odd'); -- Example 1: This Query uses a standard inner join to match rows in -- two tables and culls the rows from the first (LEFT) table -- that doesn't have an entry in the other (RIGHT) table. SELECT Nums.x, Kinds.k FROM Nums JOIN Kinds ON Kinds.r=(Nums.x-((Nums.x / 2) * 2)); -- x | k -- ------------ | ----- -- 1 | Odd -- 3 | Odd -- 5 | Odd -- 7 | Odd -- 9 | Odd -- Example 2: This Query is word-for-word the exact same but uses a -- LEFT join to match rows in two tables and add the rows -- from the second (RIGHT) table to the first (LEFT) table -- if a match exists, if it doesn't, then it adds NULL, but -- it never culls the LEFT table in any way: SELECT Nums.x, Kinds.k FROM Nums LEFT JOIN Kinds ON Kinds.r=(Nums.x-((Nums.x / 2) * 2)); -- x | k -- ------------ | ------ -- 1 | Odd -- 2 | Null -- 3 | Odd -- 4 | Null -- 5 | Odd -- 6 | Null -- 7 | Odd -- 8 | Null -- 9 | Odd -- 10 | Null -- Example 3: So, if we do not really care about whether to DISPLAY the -- oddness of the values, but only wish to list the EVEN numbers -- (aka the non-Odd numbers), I could use that LEFT JOIN and -- check for those NULLS in ANY of the joined columns, even the -- same that were used in the ON clause: -- (this kind of represents what the OP's query did): SELECT Nums.x FROM Nums LEFT JOIN Kinds ON Kinds.r=(Nums.x-((Nums.x / 2) * 2)) WHERE Kinds.r IS NULL; -- x -- ------------ -- 2 -- 4 -- 6 -- 8 -- 10 Hope that helps someone - Cheers, Ryan.
