3 ms·
The basic setup is that you have some data in two different tables, and you want to get information from both. A left join will take (select * from tableA left
by tsimionescu 5y ago
The basic setup is that you have some data in two different tables, and you want to get information from both. A left join will take (select * from tableA left join tableB on $condition) will return data where each item has all of the columns from tableA and all of the columns from tableB. Each row will be a row from tableA, with all of tableA's columns filled in. Then, the tableB columns will be filled in for that row as well if there was a "matching" row in table B (according to $condition, which is any condition you want, like in an if statement). For the rows of A that didn't have an equivalent in B, all of the tableB columns will be filled in with NULLs.
In contrast, you have 4 more Joins: the Right Join is identical to the Left Join, except that it flips the direction, so it's never necessary.
An Inner Join will only return rows that match the condition in both tables, similar in concept to a set intersection. This is a very commonly useful operation, but it can be replaced by something like "select * from tableA left join tableB on $condition where tableB.Column is not NULL".
Then, there is the "Full Outer Join", which gives you all rows in both tables; all of the rows that match appear only once and have all columns filled in, while all those in tableA that don't match still appear, and same for tableB, with NULLs for the columns of the other table. This can be obtained by doing a UNION of (tableA left join tableB) and (tableB left join tableA).
Finally, there is the cross join, which is basically the cross product of the rows of the two tables. That is, for each row in tableA, you get a row with columns set to the values of that row in tableA, and then the columns from tableB set to the values of each other row in tableB (so, you have m*n rows, if tableA has m rows and tableB n rows). This is very very rarely useful in practice.