4 ms·

As someone who doesn't work on databases - could someone please explain what is a left join any why using it exclusively is a problem?

bmn__ 5y ago
It's not necessarily a problem. It's just excentric. How would you feel about a programmer that solely uses `nand` as boolean operator, or `<` as relational operator, expending some effort to transform any expression from the domain so that it conforms to this stylistic restriction?
pizza234 5y ago
An inner (standard) join returns a row in the result, when the join finds a match between two tables, and no results when there is not match. A left join always returns at least a row; when there is no match, the part of the result (if selected) that corresponds to the right table, has NULLs. There are many important consequences of this, one of which is that left joins enforce table ordering; inner joins can be reordered by the optimizer if iterating them in a reordered way gives faster access (imagine two nested for loops, and swap the inner loop with the outer one).
deleted 5y ago
[deleted]
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.
deleted 5y ago
[deleted]
ejb999 5y ago
>>could someone please explain what is a left join any why using it exclusively is a problem? reply There is nothing wrong with it per se, it depends on the question you are trying to answer - gauging someones competence based on the absolute number of times someone uses a left join, vs right join, vs outer join during their career is silly - almost any query can be written multiple ways to get the exact same results. What matters is did the developer get the correct results. It's good to have a sense of what other join types do, but if they don't get you to the answer you want any faster or better, so what?
dghf 5y ago
Imagine we've got an EMPLOYEES table, that for each employee gives their employee id, their name, and the employee ID of their direct manager: EMPLOYEE_ID EMPLOYEE_NAME MANAGER_ID ----------- ------------- ---------- 1 Alice the CEO NULL 2 Bob the Director 1 3 Charlie the Grunt 2 We can get all employee-manager relationships with an inner join that finds every pair of rows in the table where the MANAGER_ID on one row matches the EMPLOYEE_ID on the other: SELECT emp.EMPLOYEE_NAME, mgr.EMPLOYEE_NAME AS MANAGER_NAME FROM EMPLOYEES AS emp INNER JOIN EMPLOYEES AS mgr ON emp.EMPLOYEE_ID = mgr.EMPLOYEE_ID which produces this: EMPLOYEE_NAME MANAGER_NAME ------------- ------------- Bob the Director Alice the CEO Charlie the Grunt Bob the Director Note that Alice does not appear in the left-hand column, because she has no manager, and Charlie doesn't appear in the right-hand column, because he doesn't manage anyone. If we want to list all employees, with their managers where they have them, we can use a left outer join, which returns all rows on the "left" side of the join regardless of whether they have matching rows on the "right" side -- like this (cutting out bits of the query repeated from the previous example): SELECT ... LEFT OUTER JOIN EMPLOYEES AS mgr ON ... That produces this: EMPLOYEE_NAME MANAGER_NAME ------------- ------------- Alice the CEO NULL Bob the Director Alice the CEO Charlie the Grunt Bob the Director If we want to see who (if anyone) every employee manages, we could use a right outer join, which is similar to the previous but takes rows from the "right" side whether or not there are matching rows on the "left": SELECT ... RIGHT OUTER JOIN EMPLOYEES AS mgr ON ... This produces: EMPLOYEE_NAME MANAGER_NAME ------------- ------------- Bob the Director Alice the CEO Charlie the Grunt Bob the Director NULL Charlie the Grunt (To be honest, this is even more contrived than the previous examples: you can achieve the same result by reordering things in the left outer join, and that would come more naturally to most people, including me. And you would probably swap the order you display the columns in. But I include it for completeness.) Finally, you can get a combined list of employees and their managers (if any), and potential managers and their direct reports (if any), with a full outer join, which returns rows from both sides of the join, regardless of whether they have matching rows on the other: SELECT ... FULL OUTER JOIN EMPLOYEES AS mgr ON ... Producing: EMPLOYEE_NAME MANAGER_NAME ------------- ------------- Alice the CEO NULL Bob the Director Alice the CEO Charlie the Grunt Bob the Director NULL Charlie the Grunt Using a left join exclusively isn't a problem, as long as it's returning the data you need. If you use a left join where your data requirements actually call for a different type of join (e.g. "exclude any employees without a manager"), it could be a problem. You could get round it by adding a WHERE clause (e.g. "WHERE mgr.EMPLOYEE_ID IS NOT NULL"), but that's a bit ugly and hacky.
deleted 5y ago
[deleted]
kthejoker2 5y ago
Forget about the specifics - in this case it's Maslow's hammer. Different types of joins exist for a reason - they address different requirements. Using one type of join even when those different requirements need to be met is suboptimal. In LEFT JOIN's case, it's usually not fatal (i.e. completely unable to meet the requirements) but it will definitely make things perform slower, be more complex to maintain, be harder to explain why it was chosen to other engineers (see OP), etc. Don't leave tools in your toolbox.

bmn__ 5y ago

It's not necessarily a problem. It's just excentric. How would you feel about a programmer that solely uses `nand` as boolean operator, or `<` as relational operator, expending some effort to transform any expression from the domain so that it conforms to this stylistic restriction?

pizza234 5y ago

An inner (standard) join returns a row in the result, when the join finds a match between two tables, and no results when there is not match. A left join always returns at least a row; when there is no match, the part of the result (if selected) that corresponds to the right table, has NULLs. There are many important consequences of this, one of which is that left joins enforce table ordering; inner joins can be reordered by the optimizer if iterating them in a reordered way gives faster access (imagine two nested for loops, and swap the inner loop with the outer one).

deleted 5y ago

[deleted]

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.

deleted 5y ago

[deleted]

ejb999 5y ago

>>could someone please explain what is a left join any why using it exclusively is a problem? reply There is nothing wrong with it per se, it depends on the question you are trying to answer - gauging someones competence based on the absolute number of times someone uses a left join, vs right join, vs outer join during their career is silly - almost any query can be written multiple ways to get the exact same results. What matters is did the developer get the correct results. It's good to have a sense of what other join types do, but if they don't get you to the answer you want any faster or better, so what?

dghf 5y ago

Imagine we've got an EMPLOYEES table, that for each employee gives their employee id, their name, and the employee ID of their direct manager: EMPLOYEE_ID EMPLOYEE_NAME MANAGER_ID ----------- ------------- ---------- 1 Alice the CEO NULL 2 Bob the Director 1 3 Charlie the Grunt 2 We can get all employee-manager relationships with an inner join that finds every pair of rows in the table where the MANAGER_ID on one row matches the EMPLOYEE_ID on the other: SELECT emp.EMPLOYEE_NAME, mgr.EMPLOYEE_NAME AS MANAGER_NAME FROM EMPLOYEES AS emp INNER JOIN EMPLOYEES AS mgr ON emp.EMPLOYEE_ID = mgr.EMPLOYEE_ID which produces this: EMPLOYEE_NAME MANAGER_NAME ------------- ------------- Bob the Director Alice the CEO Charlie the Grunt Bob the Director Note that Alice does not appear in the left-hand column, because she has no manager, and Charlie doesn't appear in the right-hand column, because he doesn't manage anyone. If we want to list all employees, with their managers where they have them, we can use a left outer join, which returns all rows on the "left" side of the join regardless of whether they have matching rows on the "right" side -- like this (cutting out bits of the query repeated from the previous example): SELECT ... LEFT OUTER JOIN EMPLOYEES AS mgr ON ... That produces this: EMPLOYEE_NAME MANAGER_NAME ------------- ------------- Alice the CEO NULL Bob the Director Alice the CEO Charlie the Grunt Bob the Director If we want to see who (if anyone) every employee manages, we could use a right outer join, which is similar to the previous but takes rows from the "right" side whether or not there are matching rows on the "left": SELECT ... RIGHT OUTER JOIN EMPLOYEES AS mgr ON ... This produces: EMPLOYEE_NAME MANAGER_NAME ------------- ------------- Bob the Director Alice the CEO Charlie the Grunt Bob the Director NULL Charlie the Grunt (To be honest, this is even more contrived than the previous examples: you can achieve the same result by reordering things in the left outer join, and that would come more naturally to most people, including me. And you would probably swap the order you display the columns in. But I include it for completeness.) Finally, you can get a combined list of employees and their managers (if any), and potential managers and their direct reports (if any), with a full outer join, which returns rows from both sides of the join, regardless of whether they have matching rows on the other: SELECT ... FULL OUTER JOIN EMPLOYEES AS mgr ON ... Producing: EMPLOYEE_NAME MANAGER_NAME ------------- ------------- Alice the CEO NULL Bob the Director Alice the CEO Charlie the Grunt Bob the Director NULL Charlie the Grunt Using a left join exclusively isn't a problem, as long as it's returning the data you need. If you use a left join where your data requirements actually call for a different type of join (e.g. "exclude any employees without a manager"), it could be a problem. You could get round it by adding a WHERE clause (e.g. "WHERE mgr.EMPLOYEE_ID IS NOT NULL"), but that's a bit ugly and hacky.

deleted 5y ago

[deleted]

kthejoker2 5y ago

Forget about the specifics - in this case it's Maslow's hammer. Different types of joins exist for a reason - they address different requirements. Using one type of join even when those different requirements need to be met is suboptimal. In LEFT JOIN's case, it's usually not fatal (i.e. completely unable to meet the requirements) but it will definitely make things perform slower, be more complex to maintain, be harder to explain why it was chosen to other engineers (see OP), etc. Don't leave tools in your toolbox.