Isn't SQL a Left Join B, Just A

Isn't SQL A left join B, just A?

No, since it's a join, it can produce cardinalities greater than 1 for the rows in A. That is, if there are multiple matching rows from B then a row in A will show up multiple times.

Example:

Table A:

id name
-- -------
 1 Alice
 2 Malcolm
 3 Kelly

Table B:

id_a preferred_food
---- --------------
   1 Pizza
   2 Burger
   2 Steak
   2 Menestroni

Then "A left join B" will give you:

id name    id_a preferred_food
-- ------- ---- --------------
 1 Alice      1 Pizza
 2 Malcolm    2 Burger
 2 Malcolm    2 Steak
 2 Malcolm    2 Menestroni
 3 Kelly   null null

In short:

• All rows from A show up in the left join: even 3 Kelly shows up.
• Columns from B will show up with nulls when there are no matching rows in B: row 3 Kelly has null in the last two columns.
• Rows in A may show up multiple times when they have multiple matches in B: row 2 Malcolm shows up three times.

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

As per the documentation: FROM (Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets). In this specific case, whether you specify OUTER or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will make a difference.

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here's a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.

SQL a left join b - b right join a: difference in order

I sometimes read that the order in which the tuples are returned is insignificant. The order in which a real life database returns your records, may change because the engine decides it has found a better path, using an index or not, because a block of data has been moved, ... There are big differences between the relational theory and the database of your choice. I don't mean MySQL with that.

Why and when a LEFT JOIN with condition in WHERE clause is not equivalent to the same LEFT JOIN in ON?

The on clause is used when the join is looking for matching rows. The where clause is used to filter rows after all the joining is done.

An example with Disney toons voting for president:

declare @candidates table (name varchar(50));
insert @candidates values 
    ('Obama'), 
    ('Romney');
declare @votes table (voter varchar(50), voted_for varchar(50));
insert @votes values 
    ('Mickey Mouse', 'Romney'),
    ('Donald Duck', 'Obama');

select  *
from    @candidates c
left join    
        @votes v
on      c.name = v.voted_for
        and v.voter = 'Donald Duck'

This still returns Romney even though Donald didn't vote for him. If you move the condition from the on to the where clause:

select  *
from    @candidates c
left join    
        @votes v
on      c.name = v.voted_for
where   v.voter = 'Donald Duck'

Romney will no longer be in the result set.

Difference of FROM a LEFT JOIN b vs. FROM a, b

They return different results.

A LEFT JOIN statement will return rows even if there is no associated records in table_b that match table_a id. So it will return all rows in table_a, paired with EITHER a matching row in table_a OR a blank/null table_b row (if for that row in table_a there isn't any matching row in table_b).

The second query is a shortcut for an INNER JOIN. This query will ONLY exclusively return rows that match the condition a.id = b.id. The second query can also be written as:

SELECT a.*, b.id AS b_id
FROM table_a a
INNER JOIN table_b b 
ON a.id = b.id

To answer your performance question, please see the answers on a related SO thread here.

LEFT JOIN to take all rows from left table irrespective of join condition

Move your WHERE to your ON (join predicate):

SELECT Table1.Aid, Table2.Bid, Table2.IssueId
FROM Table1 LEFT JOIN Table2
ON Table1.Aid = Table2.Bid
   AND Table2.IssueId IN ('a','b');

A WHERE that filters on the right side table essentially makes your LEFT JOIN into an INNER JOIN. It filters out the NULL rows.

Keep in mind, this will now return NULL for the Table2.Bid = 3 row, since it doesn't meet the Table2.IssueId IN ('a','b') condition.

Restricting a LEFT JOIN

SELECT ...
FROM ....
LEFT JOIN ( a INNER JOIN b ON .... ) ON ....

---

Related Topics