This article was written by Christina Kopecky and was originally published at Educative, Inc.
Structured Query Language (SQL) allows us to perform some sort of action on a single table in a relational database. These actions can update, create, delete or select a record in that table.
What if we had two tables that had different information about the same person, and we wanted to use all of that information to display on that person's invoice? We would need to use a join clause for that.
In this tutorial, we will define what a join clause is, talk about the types of join clauses, and give join examples for each.
- What are SQL Joins?
- Types of SQL Joins
- Inner Joins
- Right Joins
- Left Joins
- Full Joins
- What to learn next
What are SQL Joins?
SQL join statements allow us to access information from two or more tables at once. They also keep our database normalized. Normalization allows us to keep data redundancy low so that we can decrease the amount of data anomalies in our application when we delete or update a record.
Simplified: A JOIN clause allows us to combine rows from two or more tables based on a related column.
Let's use the example above with our customer and the customer’s order to illustrate. If we had a Customers table that had information about our customer and a separate orders table:
In these tables, take notice that there is a lot of the same information in both tables. A join statement greatly reduces the need for these duplicate values. Our new tables could look like this:
We can query the database by using join clauses to select information from the Customers table and information from the Orders table to use where we need to in our application.
There are several different types of join statements depending on your needs. In the next section we’ll take a look at examples of each type.
Types of Join statements
The type of join statement you use depends on your use case. There are four different types of join operations:
- (INNER) JOIN: Returns dataset that have matching values in both tables
- LEFT (OUTER) JOIN: Returns all records from the left table and matched records from the right s
- RIGHT (OUTER) JOIN: Returns all records from the right table and the matched records from the left
- FULL (OUTER) JOIN: Returns all records when there is a match in either the left table or right table
Inner Joins
If you were to think of each table as a separate circle in a Venn diagram, the inner join would be the shaded area where both circles intersect.
The INNER JOIN keyword selects all rows from the tables as long as a join condition satisfies. This keyword will create a result-set made up of combined rows from both tables where a common field exists.
Here is the syntax for an inner join:
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
This example will leave out any entries that have NULL values. The code here has been simplified. Please see the original post for the full code.
create table Customers (
customer_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
address VARCHAR(50),
city VARCHAR(50),
state VARCHAR(50),
zip_code VARCHAR(50),
email VARCHAR(50),
PRIMARY KEY(customer_id)
);
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (1, 'Windham', 'McKevitt', '73 Namekagon Park', 'Washington', 'DC', '20525', 'wmckevitt0@example.com');
create table Orders (
order_id INT,
order_date VARCHAR(50),
amount VARCHAR(50),
customer_id INT,
PRIMARY KEY(order_id)
);
insert into Orders (order_id, order_date, amount, customer_id) values (34, '07-11-2020', '$56.34', 1);
select first_name, last_name, order_date, amount
from Customers c
inner join Orders o
on c.customer_id = o.customer_id
ORDER BY order_date
Right outer Joins
This join statement takes all the records from Table B whether or not they have NULL values and the matching columns from Table A.
Right join returns all the rows of the rightmost table of and the matching rows for the leftmost table. RIGHT JOIN is also known as RIGHT OUTER. Here is the syntax:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Here, our Customers table is Table A and the Orders table is Table B. The code here has been simplified. Please see the original post for the full code.
create table Customers (
customer_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
address VARCHAR(50),
city VARCHAR(50),
state VARCHAR(50),
zip_code VARCHAR(50),
email VARCHAR(50),
PRIMARY KEY(customer_id)
);
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (1, 'Windham', 'McKevitt', '73 Namekagon Park', 'Washington', 'DC', '20525', 'wmckevitt0@example.com');
create table Orders (
order_id INT,
order_date VARCHAR(50),
amount VARCHAR(50),
customer_id INT,
PRIMARY KEY(order_id)
);
insert into Orders (order_id, order_date, amount, customer_id) values (34, '07-11-2020', '$56.34', 1);
select first_name, last_name, order_date, amount
from Customers c
right join Orders o
on c.customer_id = o.customer_id
ORDER BY order_date;
Left outer Joins
Left join is similar to right join. Left join returns all the rows of the leftmost table and the matching rows for the rightmost table. Below is the syntax:
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
In this example, all of the records from the Customers table are listed (whether or not they have NULL values) along with the matching columns in the Orders table. The code here has been simplified. Please see the original post for the full code.
create table Customers (
customer_id INT,
first_name VARCHAR(50),
last_name VARCHAR(50),
address VARCHAR(50),
city VARCHAR(50),
state VARCHAR(50),
zip_code VARCHAR(50),
email VARCHAR(50),
PRIMARY KEY(customer_id)
);
insert into Customers (customer_id, first_name, last_name, address, city, state, zip_code, email) values (1, 'Windham', 'McKevitt', '73 Namekagon Park', 'Washington', 'DC', '20525', 'wmckevitt0@example.com');
create table Orders (
order_id INT,
order_date VARCHAR(50),
amount VARCHAR(50),
customer_id INT,
PRIMARY KEY(order_id)
);
insert into Orders (order_id, order_date, amount, customer_id) values (34, '07-11-2020', '$56.34', 1);
select first_name, last_name, order_date, amount
from Customers c
left join Orders o
on c.customer_id = o.customer_id
ORDER BY order_date;
Full Joins
Full joins are also known as full outer joins. This basically means that a query would combine data and return records from both tables no matter if they had NULL values.
FULL JOIN creates a result-set by combining the results of the left and right joins, including all the rows. For the rows that do not match. the result-set (joined table) will shows NULL values. The syntax is as follows:
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;
Note: Full joins are not typically used, which may explain why MySQL doesn’t have support for one. There are some use cases, however.
For example, with view entries where an order is not associated with a customer, or a customer that has not made any orders.
What to learn next
Congrats on learning how to do Joins in SQL. This simple skill can make your SQL coding a whole lot easier. But there is still more to learn. The next step to take are:
- Cross join
- Joins with wildcard
- Joins on foreign keys
- Advanced SQL
Want more blogs? Sign up for our Blog Newsletter for bi-monthly collections of coding tips, top articles, featured writers, and more.
Happy learning!
Top comments (1)
Thanks for this very useful post. I just wanted to add that, there is a very easy way now, to test all the SQLs described here, using the free & portable tools, mentioned in my latest post here : dev.to/linuxguist/learn-sql-quickl...