Three Table Join with Joins Other Than Inner Join

Three table join with joins other than INNER JOIN

Yes, I do use all three of those JOINs, although I tend to stick to using just LEFT (OUTER) JOINs instead of inter-mixing LEFT and RIGHT JOINs. I also use FULL OUTER JOINs and CROSS JOINs.

In summary, an INNER JOIN restricts the resultset only to those records satisfied by the JOIN condition. Consider the following tables

EDIT: I've renamed the Table names and prefix them with @ so that Table Variables can be used for anyone reading this answer and wanting to experiment.

If you'd also like to experiment with this in the browser, I've set this all up on SQL Fiddle too;

@Table1

id | name
---------
1  | One
2  | Two
3  | Three
4  | Four

@Table2

id | name
---------
1  | Partridge
2  | Turtle Doves
3  | French Hens
5  | Gold Rings

SQL code

DECLARE @Table1 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25))

INSERT INTO @Table1 VALUES(1, 'One');
INSERT INTO @Table1 VALUES(2, 'Two');
INSERT INTO @Table1 VALUES(3, 'Three');
INSERT INTO @Table1 VALUES(4, 'Four');

DECLARE @Table2 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25))

INSERT INTO @Table2 VALUES(1, 'Partridge');
INSERT INTO @Table2 VALUES(2, 'Turtle Doves');
INSERT INTO @Table2 VALUES(3, 'French Hens');
INSERT INTO @Table2 VALUES(5, 'Gold Rings');

An INNER JOIN SQL Statement, joined on the id field

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
INNER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

Results in

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens

A LEFT JOIN will return a resultset with all records from the table on the left hand side of the join (if you were to write out the statement as a one liner, the table that appears first) and fields from the table on the right side of the join that match the join expression and are included in the SELECT clause. Missing details will be populated with NULL

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
LEFT JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

Results in

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens
4  | Four | NULL

A RIGHT JOIN is the same logic as a LEFT JOIN but will return all records from the right-hand side of the join and fields from the left side that match the join expression and are included in the SELECT clause.

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
RIGHT JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

Results in

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens
NULL| NULL| Gold Rings

Of course, there is also the FULL OUTER JOIN, which includes records from both joined tables and populates any missing details with NULL.

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
FULL OUTER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id

Results in

id | name | name
----------------
1  | One  | Partridge
2  | Two  | Turtle Doves
3  | Three| French Hens
4  | Four | NULL
NULL| NULL| Gold Rings

And a CROSS JOIN (also known as a CARTESIAN PRODUCT), which is simply the product of cross applying fields in the SELECT statement from one table with the fields in the SELECT statement from the other table. Notice that there is no join expression in a CROSS JOIN

SELECT 
    t1.id,
    t1.name,
    t2.name
FROM
    @Table1 t1
CROSS JOIN
    @Table2 t2

Results in

id | name  | name
------------------
1  | One   | Partridge
2  | Two   | Partridge
3  | Three | Partridge
4  | Four  | Partridge
1  | One   | Turtle Doves
2  | Two   | Turtle Doves
3  | Three | Turtle Doves
4  | Four  | Turtle Doves
1  | One   | French Hens
2  | Two   | French Hens
3  | Three | French Hens
4  | Four  | French Hens
1  | One   | Gold Rings
2  | Two   | Gold Rings
3  | Three | Gold Rings
4  | Four  | Gold Rings

EDIT:

Imagine there is now a Table3

@Table3

id | name
---------
2  | Prime 1
3  | Prime 2
5  | Prime 3

The SQL code

DECLARE @Table3 TABLE (id INT PRIMARY KEY CLUSTERED, [name] VARCHAR(25))

INSERT INTO @Table3 VALUES(2, 'Prime 1');
INSERT INTO @Table3 VALUES(3, 'Prime 2');
INSERT INTO @Table3 VALUES(5, 'Prime 3');

Now all three tables joined with INNER JOINS

SELECT 
    t1.id,
    t1.name,
    t2.name,
    t3.name
FROM
    @Table1 t1
INNER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id
INNER JOIN
    @Table3 t3
    ON 
        t1.id = t3.id

Results in

id | name | name         | name
-------------------------------
2  | Two  | Turtle Doves | Prime 1
3  | Three| French Hens  | Prime 2

It might help to understand this result by thinking that records with id 2 and 3 are the only ones common to all 3 tables and are also the field we are joining each table on.

Now all three with LEFT JOINS

SELECT 
    t1.id,
    t1.name,
    t2.name,
    t3.name
FROM
    @Table1 t1
LEFT JOIN
    @Table2 t2
    ON 
        t1.id = t2.id
LEFT JOIN
    @Table3 t3
    ON 
        t1.id = t3.id

Results in

id | name | name         | name
-------------------------------
1  | One  | Partridge    | NULL
2  | Two  | Turtle Doves | Prime 1
3  | Three| French Hens  | Prime 2
4  | Four | NULL         | NULL

Joel's answer is a good explanation for explaining this resultset (Table1 is the base/origin table).

Now with a INNER JOIN and a LEFT JOIN

SELECT 
    t1.id,
    t1.name,
    t2.name,
    t3.name
FROM
    @Table1 t1
INNER JOIN
    @Table2 t2
    ON 
        t1.id = t2.id
LEFT JOIN
    @Table3 t3
    ON 
        t1.id = t3.id

Results in

id | name | name         | name
-------------------------------
1  | One  | Partridge    | NULL
2  | Two  | Turtle Doves | Prime 1
3  | Three| French Hens  | Prime 2

Although we do not know the order in which the query optimiser will perform the operations, we will look at this query from top to bottom to understand the resultset. The INNER JOIN on ids between Table1 and Table2 will restrict the resultset to only those records satisfied by the join condition i.e. the three rows that we saw in the very first example. This temporary resultset will then be LEFT JOINed to Table3 on ids between Table1 and Tables; There are records in Table3 with id 2 and 3, but not id 1, so t3.name field will have details in for 2 and 3 but not 1.

How can I join 3 tables in SQL using outer joins?

I'd like to talk a little about joins, with the aim of drawing a more clear distinction in your mind of how they work:

INNER JOINs do not result in "all combinations of rows" - that's what a CROSS JOIN does

OUTER JOINs are useful where you have one table with all the values(the solid table), and you need to join it to other tables that don't necessarily have any row that is related to the solid table - these are sparse tables (holes in the data). Outer joins come in 3 flavours: left, right and full. Left and right are the same thing, you just pick the appropriate keyword depending on the order of your tables. Essentially a left join b and b right join a amount to the same thing - the keyword left/right declares which table you consider to be solid and which you consider to be sparse.

An inner join of a solid and sparse table pair would eliminate some solid rows in the results leaving only the sparse rows and the related solid rows. An outer join would represent all solid rows, and empty cells where no matching sparse row was found

Natural JOINs are JOINs where you don't explicitly state how the tables are related, you just let the database guess based on equal column names in both tables. Don't use natural JOINs, theyre nearly always a stupid idea and a pointless "optimisation" that saves a few seconds of keystrokes in exchange for hours of frustrating debugging

Your query here doesn't seem to require outer JOINs, as you state you want to know which pieces are provided by which company.. that's just

SELECT c.* FROM
  Provider r 
  JOIN Provides s ON r.code = s.provider
  JOIN Pieces c ON c.code = s.piece
WHERE 
  R.Name = 'Mattel'

Edit:
Philipxy has made a reasonable point that the keywords "solid" and "sparse" have particular meanings in maths, and I'd like to point out that I'm using them here to convey a concept rather than drag that particular aspect of maths into it. As you're looking for understanding, I thought I'd discuss things in this way because I've classically found it an easy way for learners to grasp the concepts, but these are essentially my own terms for referring to the shape of the database data you're working with and I don't necessarily guarantee that a future fellow professional who is heavily invested in his understanding of mathematics will be able to grasp the meaning of what you're saying, if you use keywords that he only uses/understands in a particular way. In that case you may need to add a similar disclaimer if you're using "solid" and "sparse" that you simply mean them in the generally accepted English Language meanings, rather than the mathematical sense

SQL Inner-join with 3 tables?

You can do the following (I guessed on table fields,etc)

SELECT s.studentname
    , s.studentid
    , s.studentdesc
    , h.hallname
FROM students s
INNER JOIN hallprefs hp
    on s.studentid = hp.studentid
INNER JOIN halls h
    on hp.hallid = h.hallid

Based on your request for multiple halls you could do it this way. You just join on your Hall table multiple times for each room pref id:

SELECT     s.StudentID
    , s.FName
    , s.LName
    , s.Gender
    , s.BirthDate
    , s.Email
    , r.HallPref1
    , h1.hallName as Pref1HallName
    , r.HallPref2 
    , h2.hallName as Pref2HallName
    , r.HallPref3
    , h3.hallName as Pref3HallName
FROM  dbo.StudentSignUp AS s 
INNER JOIN RoomSignUp.dbo.Incoming_Applications_Current AS r 
    ON s.StudentID = r.StudentID 
INNER JOIN HallData.dbo.Halls AS h1 
    ON r.HallPref1 = h1.HallID
INNER JOIN HallData.dbo.Halls AS h2
    ON r.HallPref2 = h2.HallID
INNER JOIN HallData.dbo.Halls AS h3
    ON r.HallPref3 = h3.HallID

SQL joining three tables, join precedence

All kinds of outer and normal joins are in the same precedence class and operators take effect left-to-right at a given nesting level of the query. You can put the join expression on the right side in parentheses to cause it to take effect first. Remember that you will have to move the ON clauses around so that they stay with their joins—the join in parentheses takes its ON clause with it into the parentheses, so it now comes textually before the other ON clause which will be after the parentheses in the outer join statement.

(PostgreSQL example)

In

SELECT * FROM a LEFT JOIN b ON (a.id = b.id) JOIN c ON (b.ref = c.id);

the a-b join takes effect first, but we can force the b-c join to take effect first by putting it in parentheses, which looks like:

SELECT * FROM a LEFT JOIN (b JOIN c ON (b.ref = c.id)) ON (a.id = b.id);

Often you can express the same thing without extra parentheses by moving the joins around and changing the direction of the outer joins, e.g.

SELECT * FROM b JOIN c ON (b.ref = c.id) RIGHT JOIN a ON (a.id = b.id);

Inner Join of three different Tables

You should always specify the columns to return, especially as the tables contain identical column names

SELECT p.Par_Id, p.Pat_Name, p.Pat_Gender,
    h.His_Id, h.Treated_By,
    t.Treat_Id, t.Treat_Type, t.Charges
FROM Patient p 
INNER JOIN History h 
    ON p.PAR_ID = h.PAT_ID
INNER JOIN Treatment t
    ON h.HIS_ID = t.HIS_ID AND p.PAR_ID = h.PAT_ID

Can you join three tables on the same key if that key could be missing from one or more of the tables?

Here's a version that demonstrates a fully three way full outer join. A successful query will return seven rows:

+------+  +------+  +------+     +------+------+------+
| colA |  | colB |  | colC |     | colA | colB | colC |
+------+  +------+  +------+     +------+------+------+
|    1 |  |    2 |  |    4 |     |    1 | null | null |
|    3 |  |    3 |  |    5 | --> | null |    2 | null |
|    5 |  |    6 |  |    6 |     |    3 |    3 | null |
|    7 |  |    7 |  |    7 |     | null | null |    4 |
+------+  +------+  +------+     |    5 | null |    5 |
                                 | null |    6 |    6 |
                                 |    7 |    7 |    7 |
                                 +------+------+------+

select ColA, ColB, ColC
  from TableA
  full join TableB
    on ColA=ColB
  full join TableC
    on ColC=coalesce(ColA, ColB)
order by coalesce(ColA, ColB, ColC);

The secret is to coalesce the prior table keys in the join condition of each additional table.

You can see it in action here

JOIN three tables

try something like this

SELECT A.* FROM TableA A
LEFT OUTER JOIN TableB B ON (A.id = B.a_id)
LEFT OUTER JOIN TableC C ON (A.id = C.a_id)
WHERE B.a_id IS NOT NULL 
  OR c.a_id IS NOT NULL

Combine three tables with join an two with union

You need a join with subquery for union based on id

Select 
    t1.id, t1.naam, t2.lastname, t.Function 
From 
    t1
Left Join 
    t2 On t2.t1 - id = t1.[id]
Left Join 
    t3 On t3.t2 - id = t2.[id]
Inner Join 
    (Select id, Function
     From t2
     Union 
     Select id, Function
     From t3) t On t.id = t1.id 

JOIN three tables even if one is empty

Replacing "JOIN", which implies "INNER JOIN", by "LEFT JOIN" should do the trick.

---

Related Topics