SQL 4 beginners - JOIN

In most online articles about databases you can easily find a lot of articles explaining cool, complex and new features. But what’s about some basics? I decided to release some articles about the basics. The articles will mostly cover topics usable in most relational databases, but the examples and the focus will be on MS SQL Server database engine.

Let’s find out what is a JOIN and how to use it.

The main difference between JOIN and UNION operator is that union combines results vertically, while the JOIN combines result's horizontally. There are different types of JOIN-s depending on the business need and we will discuss about them. Since the databases should be normalized it is essential to use joins to get details for some records.

Main types of joins:

  • INNER JOIN - only returns those records that have matches in both tables A and B
  • LEFT/RIGHT JOIN - returns all the records in the A table (left or right) whether they have a match in the B table or not (right or left)
  • CROSS JOIN - joins everything from table A with everything from table B

In various examples across the internet there are mentioned also other types of join's but the mentioned three are basic and easy to understand.

If you want to find a little bit you can see one of the following articles:

 To be able to explain joins on a concrete example we will use two tables. One table will contain a list of persons and the second one a list of some pet types.

INNER JOIN

Like said before INNER JOIN returns those records that have matches in both tables A and B. You need to have a key on both tables that you can join using ON statement.

In our example, we can join the two tables using "Pet type" column and find out if our pet like to walk. Since Ivan does not have a pet he will not be returned because there must be a match in both tables. Also, the "Fish" will not be returned because none of our entries does not have a pet of type fish.

Example:

SELECT 
	P.Name AS [Owner name]
	, P.[Pet type]
	, T.[Like to walk]
FROM 
	#Person P
	INNER JOIN #Pet_type T 
		ON P.[Pet type] = T.[Pet type]

LEFT/RIGHT JOIN

The LEFT JOIN will return all records from the left table (A) and matched records from the right table (B). If there are no corresponding records on the right side, NULL values will be returned. Just like the INNER JOIN, the LEFT JOIN requires a joining key ON.

Using the LEFT JOIN in our example we will get the full list of persons and the "Like to walk" column will be NULL if the person does not own a pet.

Example:

SELECT 
	P.Name AS [Owner name]
	, P.[Pet type]
	, T.[Like to walk]
FROM 
	#Person P
	LEFT JOIN #Pet_type T 
		ON P.[Pet type] = T.[Pet type]

The RIGHT JOIN does the same job but in reverse.

The LEFT/RIGHT join is often used to determinate what values from one table have/haven't pairs in the second table.

Example:

SELECT 
	P.Name AS [Owner name]
	, P.[Pet type]
	, T.[Like to walk]
FROM 
	#Person P
	LEFT JOIN #Pet_type T 
		ON P.[Pet type] = T.[Pet type]
WHERE
	T.[Pet type] IS NULL

The result of this query would be all persons that don't have a pet, but also the persons with a pet type who does not exist in the "Pet type" table. Just add a person with a new pet type and check it by yourself.

CROSS JOIN

Mathematically speaking the CROSS JOIN is a Cartesian Product. It combines every record from the first table with every record from the second table. The CROSS JOIN doesn't need a joining KEY. It can result in a very big data set, so use it with caution. The resulting number of rows will be equivalent of number of rows from the first table multiplied by the number of rows in the second table. The advice is to limit the result set using the WHERE statement, but sometimes this is maybe exactly what you want.

When joining two tables you can combine them using CROSS JOIN or just ",".

... FROM TABLE_A CROSS JOIN TABLE_B ... <=> ... FROM TABLE_A, TABLE_B ...

Example:

SELECT 
	P.Name AS [Owner name]
	, P.[Pet type]
	, T.[Like to walk]
FROM 
	#Person P
	CROSS JOIN #Pet_type T 

*You can download the complete SQL Script with all examples from the post here: SQL Script.sql (2.99 kb)