One of the things you’ll need to use the most when writing stored procedures and views in SQL are joins. These allow you to retrieve data from multiple tables at once. This article will be mostly focusing on T-SQL and SQL Server Management Studio.
From my experience people can get slightly overwhelmed by the numbers of joins available and how they differ to one another. This is going to be a quick and dirty introduction to each and how they work.
Our example:
Table 1: Owners
Table 2: Pets
As you might be able to tell, the Pets.Owner column corresponds to the Owner.ID column.
Left Outer Join
An outer join will return all the data from both tables, matching any data that belongs to each other and still providing the data that doesn’t. You have to decide which table you want to use to search for matching data.
SELECT * FROM Owners
LEFT JOIN Pets on Owners.ID = Pets.Owner
That gives us the following table:
As you can see, John Doe does not have any pets so while his data still shows up, the corresponding columns for the Pet table show up as NULL values.
Right Outer Join
Using almost an identical query as above we get a slightly different result.
SELECT * FROM Owners
RIGHT JOIN Pets on Owners.ID = Pets.Owner
Now we’re putting the table on the right hand side of the query first and we get this table:
In this table John Doe doesn’t show up at all as all the Pets have a matching owner.
Inner Join
An inner join only matches up data that exists in both tables. It’s the most common type of join because the majority of the time developers only want the pertinent information to be returned. A query will look like this:
SELECT * FROM Owners INNER JOIN Pets on Owners.ID = Pets.Owner
This will return a similar table to the one in our Outer Right Join example, as coincidentally none of our example pets are ownerless.
An inner join should not return any owners who do not have a pet linked to them.
Cross Join
This is a bit of a weird one and is rarely used unless for load testing.
SELECT * FROM Owners CROSS JOIN Pets
This returns all rows of table B for each item in table A. In our example we have 5 rows in Pets and 5 rows in Owners so a cross join returns 25 rows:
It’s a great way of quickly filling up a test table with data.
Other Joins
So we’ve covered Inner Joins, Outer Joins and Cross Joins – what else can you do with joins?
Full Join
This join returns both tables with all the data, regardless of whether the join condition is met or not. This means that there could be NULL columns on either side of the join.
SELECT * FROM Owners FULL JOIN Pets on Owners.ID = Pets.Owner
Exclusion Joins
What if you only want to return the data that does not meet the join condition? Take our LEFT OUTER JOIN example and you can see that John Doe does not have a pet. This query would only return his data:
SELECT * FROM Owners LEFT JOIN Pets on Owners.ID = Pets.Owner WHERE Pets.Owner is NULL
Are there any joins I’ve missed? Can you think of an actual reason to use Cross Joins? Let me know in the comments
Code Chickens 