SQL Joins: What They are, How They Work, and How Many Types Exist

Did you ever need to Join two tables? An SQL Join statement lets you access data from multiple sources – tables in SQL.  

You might ask, “What is SQL?” or/and “What are SQL joins?” depending on how far down you are on the SQL path, And we’d say that’s a fair question. While it’s a very well-known acronym, it is an important part of database management systems (DBMS). SQL manages everything from giant corporate databases to small local databases. 

Think of it like this; if we have to write code and programs for each type of data because there aren’t any specialized tools to help us do this, we will drown in data clutter. If you’re creating web pages or sending emails and there are millions of different options available, you want something that will make all that information easier to read and work with — not harder! That’s what SQL does for you. 

SQL (Structured Query Language) manages data in relational database management systems (RDBMS). A developer who knows SQL can write code to create, modify, and retrieve data stored in a database.

SQL is the most widely used database language in the world. It’s an essential skill for anyone working with websites or applications that use databases to store information.

In addition to using SQL commands directly, most database applications allow you to enter SQL statements through graphical user interfaces or wizards.

In this blog post, we’ll discuss SQL joins with examples to show how you can use this technique to improve your queries.

Types of SQL Joins

SQL selects the data Join from two tables (or more) based on the same key data points  between them; the top 4 types of SQL join statements are as follows: 

1. Cross Join

A cross-join is a Cartesian product of two or more tables. A cross-join can combine data from multiple tables into a single result set. For example, if you have a table named Employees that contains employee names and phone numbers, you can cross-join with another table named Phones to get each employee’s phone numbers.

The syntax for a cross-join is as follows:

SELECT * FROM [table name 1] CROSS JOIN [table name 2]

Take a look at the clear cross-join example below.

Table 1: Car_model

	Car_model
1	Corolla
2	Cultus
3	Camry

Table 2: Color_name

	Color_name
1	Black
2	Silver
3	Red

To get all possible combinations of cars and colors, you need to execute the following piece of code:

SELECT c.Car_model ,c1.Color_name FROM Cars c CROSS JOIN Colors c1

This will generate a Cartesian product of all possible values of above 2 tables.

	Car_model	Color_name
1	Corolla	Black
2	Cultus	Black
3	Camry	Black
4	Corolla	Silver
5	Cultus	Silver
6	Camry	Silver
7	Corolla	Grey
8	Cultus	Grey
9	Camry	Grey

2. Inner Join

An inner join is a type of join that joins two tables based on the matching values in their columns. The result of the inner join contains all rows from both tables, with matching rows from both tables linked together.

To use an inner join in SQL, you use the following syntax:

SELECT * FROM table1 INNER JOIN table2 

ON table1.column_name = table2.column_name; 

Here’s how this code works:

Table 1: Foods

ITEM_ID	ITEM_NAME	ITEM_UNIT	COMPANY_ID
1	Cheeze Mix	Pcs	16
6	Cheeze It	Pcs	15
2	Biscuits	Pcs	15
3	Munch	Pcs	17

Table 2: Company

COMPANY_ID	COMPANY_NAME	COMPANY_CITY
18	Nestle	London
15	Jack Foods	Boston
16	Foodies	America
17	Akas Food	Australia

You need to execute following piece of code to get the desired outcome with Inner join:

SELECT foods.item_name,foods.item_unit, company.company_name,company.company_city FROM foods INNER JOIN company ON foods.company_id =company.company_id;

You’ll get the following output:

ITEM_NAME	ITEM_UNIT	COMPANY_NAME	COMPANY_CITY
Cheeze Mix	Pcs	Nestle	London
Cheeze It	Pcs	Jack Foods	Boston
Biscuits	Pcs	Foodies	America
Munch	Pcs	Akas Food	Australia

3. Self Join

Self-join enables the joining of a table to itself. To employ a self join, the table must contain a column (let’s call it X) that serves as the primary key and another column (let’s call it Y) that includes values that can be matched up with the values in Column X. 

Columns X and Y do not need to have identical values for any given row, and Column Y can even be null.

Here is an illustration of the members and their administration. Let’s examine the membership table:

Each member has a unique identifier, which we refer to as “Column X.” The Teamlead Id column contains the member or teamleader’s Id; this is our “Column Y.” If these columns are utilized to monitor the member-teamleader relationships in this table:

Table: Customers

Id	FullName	Salary	TeamleadId
1	Chirs Hemsworth	200000	5
2	Tom Holland	250000	5
3	Ben Affleck	120000	1

You need to execute following piece of code:

SELECT     member. Id,         member.FullName,         member.teamleadId,         teamlead.FullName as teamleadName FROM members member JOIN members teamlead ON member.teamleadId = teamlead.Id

This will show you the following output:

Id	FullName	Teamlead Id	Teamlead Name
1	Chirs Hemsworth	5	Gal Galdot
2	Tom Holland	5	Gal Galdot
3	Ben Affleck	1	Chirs Hemsworth
5	Gal Galdot	4	Christian Bale

4. Outer Join

An outer join returns matching and non-matching values from one or both tables.

Outer joins subdivide additionally into three subcategories:

1. Left outer join
2. Right outer join
3. Full outer join 

These categories depend on the table’s rows included: left, right, and both. Assume an example of Pets table and Owner table for all these Outer join types.

Table 1: Pets

ID	Pet.Name	Owner.ID
1	Neiger	1
2	Bella	2
3	Snowie	2
4	Tiger	3
5	Swiky	0

Table 2: Owers

ID	Owner.Name
1	Stephan
2	Jonny
3	Charlie
4	Vicky

• Left Outer Join

A left outer join is a way to join two tables together. Only rows from the table named before the LEFT OUTER JOIN clause that was not found in the target table are included in the output.

SELECT pets.name AS pet_name, owners.name AS owner FROM pets LEFT JOIN owners ON pets.owner_id = owners.id;

You’ll get it as:

Pet.name	Owner
Neiger	Stephan
Bella	Jonny
Snowie	Jonny
Tiger	Charlie
Swiky	NULL

• Right Outer Join

A right join (or outer join) is like the left outer join, except with the reversed table treatment. 

Let’s do the same query with right join:

SELECT pets.name AS pet_name, owners.name AS owner FROM pets RIGHT JOIN owners ON pets.owner_id = owners.id;

In this case, we retain all relevant rows from the owner’s table, and if any columns from pets require values but don’t have any, we substitute NULL.

Pet.name	Owner
Neiger	Stephan
Bella	Jonny
Snowie	Jonny
Tiger	Charlie
Null	Vicky

• Full Outer Join

This category (full outer join) combines the applying effect of both left and right (outer joins). 

There is no match in the full outer joined table rows; it appears that it will consider the result set to have NULL values for every table’s column that lacks a matching row. 

A single row will produce the result for those (rows) that have some matching possibilities.

Here’s the query for this:

SELECT pets.name AS pet_name, owners.name AS owner FROM pets FULL JOIN owners ON pets.owner_id = owners.id;

It will produce the following results:

Pet.name	Owner
Neiger	Stephan
Bella	Jonny
Snowie	Jonny
Tiger	Charlie
Swiky	Null
Null	Vicky

How to Select Data from Multiple Tables Using SQL Joins?

You can get information from as many tables as you want, specify the conditions based on any data in the tables, and display the results in any order you choose if your database is properly designed and linked.

Let’s have an example:

Table 1: Product

ID	Name	Price	Category_ID
1	Table	200	2
2	Chair	120	3
3	Bricks	150	2
4	Sofa	50	1
5	Sand	20	1
6	Toy Car	100	2

Table 2: Category

ID	Category
1	Material
2	Furniture
3	Toys

You need following code to get the desired outcome:

SELECT   product.name AS product_name,   category.name AS category_name FROM product JOIN category ON product.category_id=category.id;

After execution, you’ll get following output:

Product_name	Category
Table	Furniture
Chair	Furniture
Bricks	Material
Sofa	Furniture
Sand	Material
Toy Car	Toys

Use Cases for SQL Joins

The basic application of SQL Joins is to select data from many tables, as shown above. But, it can be applied to address different tasks. Some of the most occurring usage scenarios for SQL joins are:

• Relationship Hierarchy: Joins are used if a multi-tiered relationship exists among tables, e.g., grandfather, father, son relationship or user, city, and state relationship.
• Many-to-many Connections: This scenario occurs when many items in one table have a relationship with many items in the second table. For example, students are enrolled in multiple courses, and courses are assigned to multiple students. Same as customers subscribe to different products while products are shared by customers.
• SQL Joins for Data Analysis: From a business intelligence (BI) perspective, SQL joins analyze data for making charts and building dashboards.
• Database Normalization: In normalization, larger tables are organized into smaller tables to drop any data redundancy and anomalies. Then relevant data is fetched from these smaller tables using SQL joins. 

Learn SQL Joins with Sololearn

AS a declarative language, SQL needs joins statements to extract relevant data from highly structured databases.  . They can be confusing, especially if you’re new to SQL, but they are worth learning. We have given you different commands to help you write queries and recreate them around your databases. So, to learn more SQL join or relational databases, you must start with a basic beginner program. 

All three-course levels (beginners, intermediate, and compiler) at Sololearn will provide you with the complete coverage of SQL joins, their importance, and easy ways to imply them daily.