Quick SQL Server Joins Review

Question

I have been studying joins and I just want somebody to confirm what I understood of it.

When it comes to joins it is always ParentTable right outer join ChildTable and this is also correct: ChildTable left outer join ParentTable.

What is wrong with this: ParentTable left outer join ChildTable which will give null values?

I have made a quick select below, is it right?

Select Mom, Brother from ParentTable right outer join ChildTable
on ParentTable.LastName = ChildTable.LastName

or is it a left outer join or an inner join? I have an upcoming test next Tuesday and this is confusing me any help would be great!

`RIGHT` or `LEFT` refers to the "master" table, if you will. The table you are taking all records from, matched and unmatched. So, which table do you want to take all records from? — Andrew, Mar 30 '15 at 19:30
So the direction always points to the Parent table if that is right then the example I gave is wrong? — Tom H, Mar 30 '15 at 19:31
So it would look like this instead? Select Mom, Brother from ParentTable left outer join ChildTable on ParentTable.LastName = ChildTable.LastName — Tom H, Mar 30 '15 at 19:33
Those two queries are different. IN the first, you are taking all rows from `ParentTable`, due to the left join (`ParentTable left outer join...`). In the second, you will take all rows from `ChildTable` (`right outer join ChildTable`). Run the queries yourself, and you'll see the difference, I bet. Here's a great [visualization](http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/) to help understand this. — Andrew, Mar 30 '15 at 19:34
This is not really an actual database it is just an example I came up with at the top of my head but I do have a database here that I finished working on a month ago and it has 3 joins. I used right outer join on all of it and it gave me 2 records out of 50 which is the correct answer. Out of curiousity I replaced one right outer join into a left outer join and it is still giving me the same results. — Tom H, Mar 30 '15 at 19:39
possible duplicate of [Difference between INNER and OUTER joins](http://stackoverflow.com/questions/38549/difference-between-inner-and-outer-joins) — Tab Alleman, Mar 30 '15 at 20:05

Stephen Turner · Answer 1 · 2015-03-30T21:17:26.440

Parents and Children aren't really the best examples when you are looking at this, they make you think of that specific family 'relationship', which puts constraints on how you think.

Let's take two simpler tables:

    a:            b:
    a.id a.val    b.id b.val
    1    a1       1    b1
    2    a2       3    b3
    4    a4       5    b5
    6    a6       6    b6
    7    a7

Here is a simple left outer join:

SELECT a.*, b.* FROM a LEFT OUTER JOIN b ON a.id = b.id;

Here "a" is the left table, so all the rows from the table "a" will be included, but there will only be rows from table "b" where they match, otherwise all the fields selected from that table will be "null".

    result:
    a.id a.val b.id b.val
    1    a1    1    b1
    2    a2    null null
    4    a4    null null
    6    a6    6    b6
    7    a7    null null

Here is a simple right outer join:

SELECT a.*, b.* FROM a RIGHT OUTER JOIN b ON a.id = b.id;

Here "a" is still the left table, but because we've specified a right join all the rows from "b" will be included, but only matching rows from a.

    result:
    a.id a.val b.id b.val
    1    a1    1    b1
    null null  3    b3
    null null  5    b5
    6    a6    6    b6

LEFT and RIGHT just refer to the tables in the JOIN clause, and will both give nulls depending on what data is in the tables.

If you swap the tables and use LEFT instead of RIGHT (or vice-versa) it's just the same, so the following is the same as the first query above:

SELECT a.*, b.* FROM b RIGHT OUTER JOIN a ON a.id = b.id;

Note that the order of the tables in the SELECT or ON clauses doesn't affect the join clause.

An INNER JOIN doesn't take a LEFT or RIGHT term, as it doesn't show all records from either the left or right side, just those that match:

SELECT a.*, b.* FROM a INNER JOIN b ON a.id = b.id;

    result:
    a.id a.val b.id b.val
    1    a1    1    b1
    6    a6    6    b6

Instead of thinking of Parent and Child think of the tables just as sets of data, that is probably what is confusing you.

Quick SQL Server Joins Review

1 Answers1