MySQL JOIN Tutorial: Inner, Outer, Cross, Left, Right And Self

Learn about different MySQL JOIN statements like Inner, Outer, Cross, Left, Right, And Self with syntax and programming examples:

In this tutorial, we will learn about MySQL JOIN and also understand the different types of Joins that are supported in MySQL. JOIN in its simplest form can be thought of as a means to retrieve/update or delete data from multiple tables against a single query.

So, in essence, JOIN combines 2 or more tables to fetch data against a given condition.

MySQL also supports other different ways to query data from multiple tables using – subqueries and by combining multiple queries using UNION, etc.

=> Click here for the complete MySQL tutorial series

Normalized Tables

In normalized MySQL databases, tables have relationships against common columns through means of key constraints like Foreign keys.

Let’s try to understand this with the help of an example – Suppose there are 2 tables, EMPLOYEE and EMPLOYEE_DEPARTMENT. Now in a denormalized database – i.e. a single table having all the columns, a lot of information for example about the Department would be duplicated as there can be a lot of Employees that are part of the same Department.

Hence to reduce those duplications and save on storage, databases are kept in a normalized state.

So in this case, we would refer to the Department by a field Department_id in the EMPLOYEE table and all relevant information related to Department – like Department Info, Department head, etc may be kept as part of the EMPLOYEE_DEPARTMENT table.

So in a nutshell – EMPLOYEE and EMPLOYEE_DEPARTMENT are linked to each other through the Department_id field, which is acting as a FOREIGN KEY for the EMPLOYEE table and PRIMARY KEY for the EMPLOYEE_DEPARTMENT table.

Below image is a pictorial representation having a relationship between both these tables through means of Foreign Key Constraint

MySQL JOIN

MySQL JOIN is used to fetch, update or delete data from 2 or more tables against a given condition. Hence, JOIN is always used in conjunction with SELECT, UPDATE, or DELETE statements

Syntax of JOIN command:

JOIN with SELECT

 SELECT {column_list} FROM tableName1
{joinType} tableName2
ON {joinCondition}

JOIN with UPDATE

DELETE FROM tableName1
{joinType} tableName2
ON {joinCondition}
WHERE {condition}

JOIN with DELETE

UPDATE tableName1
SET targetColumn = targetValue
FROM tableName1
{joinType} tableName2
ON {joinCondition}
WHERE {condition}

Please note that JOIN could be applied to multiple tables in a single query, but for simplicity let’s first try to understand using Joins with 2 tables.

The different parts of the syntax include:

• {column_list} – This represents the names of columns we want to retrieve as the result of our query.
• {JoinType} – This indicates the type of Join that we are applying. There are following different types of JOINS that can fetch data:
  • INNER JOIN
  • OUTER JOIN
    • LEFT OUTER JOIN
    • RIGHT OUTER JOIN
  • CROSS JOIN

We will learn about all these different types of MySQL JOINS in upcoming sections of the tutorial.

• {JoinCondition} – This is the column conditions which would be used for the JOIN to query and fetch data.

In the next section, we will discuss different types of Joins that are available in MySQL.

Types Of MySQL JOIN

Sample Data Preparation

To use the JOINS, we will use 2 tables Employee and Employee_Department with the details below. You can use/refer to a helpful site here in order to generate dummy data for the schema.

Listing Queries for table creation and insertion:

CREATE TABLE IF NOT EXISTS Employee_Department (
	name varchar(100),
    id INT NOT NULL auto_increment,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS Employee (
	name varchar(100),
    id int not null auto_increment,
    address varchar(100),
    Department_id int,
    PRIMARY KEY (id),
    FOREIGN KEY (Department_id)
    references
    Employee_Department(id)
);

INSERT INTO `Employee_Department` VALUES ('Information Technology','1'),
('HR','2'),
('Finance','3'),
('Accounting','4'),
('Housekeeping','5'),
('Security','6'),
('Support','7'),
('Contract Staff','8'),
('Sales','9'),
('Management','10');

INSERT INTO `Employee` VALUES ('Veniam','1','640 Damon Junction\nEast Mathew, NY 68818','3'),
('Molestiae','2','6658 Hollis Club\nErnamouth, TX 19743','10'),
('Officiis','3','59965 Mason Neck Apt. 985\nKareemborough, NV 85535','9'),
('Rerum','4','91067 Geovany Fort\nHanefort, WA 92863','6'),
('Et','5','7647 Reva Shores Suite 970\nNew Audrafort, OH 17846-5397','2'),
('Et','6','9419 Carmela Burg Apt. 687\nAimeebury, SD 32389-4489','8'),
('Laborum','7','6961 Weissnat Drive\nDonnellyfort, MT 53947','6'),
('Cupiditate','8','117 Nellie Summit Suite 982\nSouth Heavenfurt, CA 45675','8'),
('Eveniet','9','9086 Mariam Square Suite 698\nSouth Dulce, MT 82861-3079','2'),
('Rerum','10','783 Goodwin Burgs Apt. 429\nWillmsfort, UT 42820-1019','9'),
('Quis','11','42928 Ernesto Trail\nEast Jules, WV 87169-2851','1'),
('Esse','12','161 Kassulke Stravenue Apt. 937\nWilliamsonton, MS 62622','7'),
('Dolores','13','22811 Liliana Trail Apt. 890\nSouth Ernesto, MT 04134','10'),
('Ut','14','981 Laron Overpass Suite 361\nWest Olahaven, FL 46982-7801','10'),
('Mollitia','15','411 Louisa Mill\nSouth Maximefort, MA 04903','8'),
('Necessitatibus','16','2853 Jonathon Turnpike\nQuitzonville, KY 54776','4'),
('Fugit','17','3647 Rosalinda Corner\nMaureenstad, RI 96605','5'),
('Corporis','18','03559 Nicholas Circle Apt. 364\nWest Zettaberg, ID 58592-3447','8'),
('Neque','19','56111 Alysson Gateway Apt. 212\nLeschbury, VT 90605-2306','8'),
('Velit','20','263 O\'Keefe Avenue Apt. 884\nWest Missouri, IL 50303','6'),

INNER JOIN

INNER JOIN is the most common form of JOIN and is very widely used. Almost all of you should have used this at some point or another. Let us see syntax and then some sample queries.

Syntax:

SELECT {column_list} FROM tableName1
INNER JOIN tableName2
ON {joinCondition}

Query to fetch Department_names for all the Employees from the above-declared tables (Employee and Employee_Department):

SELECT Employee.name as Employee_name, Employee_Department.name as Department_name
FROM Employee
INNER JOIN Employee_Department
ON Employee.Department_id = Employee_Department.id

Here’s the output of the above query:

Note: Here we have used column name aliases. Example: Employee.name as Employee_name – just to make the results more readable and comprehensive.

Let’s modify this query to fetch just the names beginning with the letter ‘m’.

SELECT Employee.name as Employee_name, Employee_Department.name as Department_name FROM Employee
INNER JOIN Employee_Department
ON Employee.Department_id = Employee_Department.id
WHERE Employee.name like 'm%'

Below is the output:

And now let’s understand the INNER JOIN with the help of the Venn Diagram as below. The resultant rows returned consist of the overlapping data between both the tables as matched against the JOIN condition.

OUTER JOIN

OUTER JOIN is used to fetch data from 2 or more tables with an exception of including unmatched rows (or rows having null data for the queried columns) as well.

To understand Outer Join better, add a new table office_locations having fields – id and address and add a new column named `office_id` to the originally created Employee table.

Here are the queries for the same:

CREATE TABLE IF NOT EXISTS office_locations (
	address varchar(100),
    id INT NOT NULL auto_increment,
    PRIMARY KEY (id)
);

ALTER TABLE Employee
ADD COLUMN office_id int;

INSERT INTO `office_locations`(address) VALUES('Bangalore'),
('Mumbai'),
('Seattle'),
('Santa Clara');

UPDATE Employee
SET office_id=1
where id % 2 = 0;

UPDATE Employee
SET office_id=2
where id % 3 = 0;

Please refer to below Venn Diagram to understand OUTER JOINS pictorially:

There are 2 types of OUTER JOINs

a) LEFT OUTER JOIN

As the name suggests, this type of Join would fetch all the rows (including NULL values) from the table which is on the left side of the JOIN query. In simple words, all results/rows that are not matching the JOIN condition will be returned with the result having NULL values for the right table.

For example, we need location data for all the Employees – i.e. find out what is the office address of all the Employees.

SELECT * from Employee
LEFT OUTER JOIN office_locations
ON Employee.office_id = office_locations.id

Output of the above query:

b) RIGHT OUTER JOIN

Similar to LEFT JOIN, in this type of Join all the records that do not match from the right table are returned with NULL values on the columns for the left side table.

For example, with our sample tables, if we run the RIGHT JOIN against the same query which we used for LEFT JOIN, we will get NULL values for cities ‘Seattle’ and ‘Santa Clara’ as there are no rows in the Employee table that have location set to these values.

SELECT * from Employee
RIGHT OUTER JOIN office_locations
ON Employee.office_id = office_locations.id

Output of the above query:

Notes/Tips:

• In other relational databases like Microsoft SQL – you may find another type of OUTER JOIN which is called FULL OUTER JOIN. It’s nothing but a combination of both INNER and OUTER Joins – i.e. it will return NULL values from both LEFT and RIGHT tables.
• For a detailed discussion along with explanation and examples around differences between INNER and OUTER JOINS please refer to our tutorial here.
• In the OUTER JOIN queries – the RIGHT OUTER JOIN and LEFT OUTER JOIN could be just specified as RIGHT JOIN and LEFT JOIN respectively for more readability.

CROSS JOIN

CROSS JOIN is also called Cartesian Product. It returns a result against matching Join conditions with a total of m x n rows where m and n are a number of matching rows in table1 and table2 against the JOIN condition.

Let’s see sample query for a CROSS JOIN for getting results from 2 tables – Employee and office_locations

SELECT * from Employee
CROSS JOIN office_locations

The output returned will contain a total of 80 rows which is nothing but the product of rows in both the tables – Employee (20) x office_locations (4) => 80

Please note that, while doing CROSS JOIN you don’t need to specify any JOIN condition as you would anyway get m x n result.

Notes/Tips:

You won’t find a lot of times using CROSS JOIN, as it does not have many use cases associated. This Join is generally executed when someone is looking to get all possible combinations against data from 2 tables.

For example: Suppose you are a garment exporter and you have 2 tables – one having color data and another having size data. Here, to ensure inventory, you can consider doing a CROSS JOIN of both the tables to ensure that all the garments are procured for all size and color combinations.

SELF JOIN

SELF JOIN is just like any other INNER or OUTER Join, with the only exceptions that both the tables i.e. the Joining and Joined table are the same.

We generally use SELF Joins when we want to find out the relationship between rows of the same table. For example, if a table has both Employee id and Supervisor Employee id, we can join the table against itself if we want to find out the manager’s name against the Employee’s name.

Please note that for SELF JOIN you should use table aliases so that Join conditions can be specified referring to the correct table.

Let’s see an example here:

SELECT e1.name as Employee1, e2.name as Employee2
from Employee e1
inner join Employee e2
on e1.office_id = e2.office_id
where e1.Department_id = e2.Department_id

Here, we have joined the Employee table to itself to find out Employee names that have the same office location and Department

As we had discussed, SELF JOIN is just having Joining and Joined tables as same, we can use SELF JOIN with INNER or OUTER Joins.

MySQL JOIN With UPDATE And DELETE

So far, we have discussed Joins with SELECT statements. However, Joins could also be used with MySQL DELETE and UPDATE statements.

The syntax remains the same here. Let’s see a couple of examples to understand the concept better.

UPDATE With INNER JOIN

Suppose we want to change the address in the Employee table to the name of the office location that is present in the office_locations table. Here we can use INNER JOIN to fetch the city name from office_locations and update the same query.

Sample query:

UPDATE Employee emp
inner join office_locations off_loc
on emp.office_id = off_loc.id
set emp.address = off_loc.address;

Similar to this, UPDATE can be used with other types of Joins as well, depending on the requirement against specific use cases.

DELETE With INNER JOIN

We will use tables, Employee and Employee_Departments as example. Suppose we want to delete all the Employee records that belong to the sales Department and we want to delete the entry for sales Department as well.

Since we used Department_id as the FOREIGN KEY constraint, hence we should first DROP that constraint before we use DELETE with JOIN for multiple tables.

Let’s first find out the name of the constraint that got created for Department_id in the Employee table. Simply run the command to get the CREATE TABLE command.

show create table Employee

You will get the output as:

CREATE TABLE `Employee` (
  `name` varchar(100) DEFAULT NULL,
  `id` int NOT NULL AUTO_INCREMENT,
  `address` varchar(100) DEFAULT NULL,
  `Department_id` int DEFAULT NULL,
  `office_id` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `Department_id` (`Department_id`),
  CONSTRAINT `Employee_ibfk_1` FOREIGN KEY (`Department_id`) REFERENCES `Employee_Department` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

Now you can refer the name of the FOREIGN_KEY constraint from the above command which is ‘Employee_ibfk_1’ and we can use this name to drop this constraint from the Employee table by running the below command:

alter table Employee
drop constraint Employee_ibfk_1

Now let’s run the DELETE command with INNER JOIN

delete emp_dept, emp
from Employee emp
inner join Employee_Department emp_dept
on emp.Department_id = emp_dept.id
where emp.Department_id = 9

The output of this command would be no. of rows deleted (and this should successfully DELETE the rows from both the tables referenced in the JOIN query above)

Frequently Asked Questions

Q #1) How many types of Joins are there in MySQL?

Answer: MySQL supports primarily 3 types of Joins. These are:

• Inner Join
• Outer Join – Left Outer Join and Right Outer Join
• Cross Join

Q #2) Can Join just be used for 2 tables?

Answer: Joins can be applied to 2 or more than 2 tables. Most of the time Joins are used for 2 tables.

Below is an example of an INNER JOIN using 3 tables:

select Employee.name as emp_name, Employee_Department.name as dept_name,
 office_locations.address as office_location
from Employee
inner join Employee_Department
on Employee.Department_id = Employee_Department.id
inner join office_locations
on Employee.office_id = office_locations.id

Output of the above query:

Q #3) How to use table aliases with Joins?

Answer: Aliases are a way to have a temporary name for a table to be referred to within a query. Since JOIN queries are at times complex and to make the queries readable and avoid referring to table names for each column reference, we can have shortened names with aliases.

Let’s write an INNER JOIN query to fetch data for all Employee names and Departments names from tables Employee and Employee_Departments respectively.

select emp.name as emp_name, Department.name as dept_name
from Employee emp
inner join Employee_Department as Department
on emp.Department_id = Department.id

In the above query, you can see that we have aliased Employee table as emp and Employee_Department table as Department and used the aliases for referencing column names and Join conditions.

Q #4) What is CROSS JOIN?

Answer: CROSS JOIN is a type of Join where the user wants to fetch the Cartesian product of the tables being Joined.

The result of CROSS JOIN is m x n where m is the no of matching rows in the first table and n is the no of matching rows in the second table.

Q #5) How to obtain FULL OUTER JOIN in MySQL

Answer: MySQL does not provide FULL OUTER JOIN as a separate Join type unlike other databases like Microsoft SQL. However, to obtain results as a FULL OUTER JOIN would have fetched, we can combine the results of LEFT OUTER JOIN and RIGHT OUTER JOIN.
For example, we can fetch Employee and Department details and apply union to Left and Right Outer Joins.

Recommended reading =>> MySQL Union Operator

Conclusion

In this tutorial, we learned about different types of Joins that are available in MySQL.

We discussed INNER, OUTER, CROSS, and SELF Joins and also saw how JOIN queries could be used with UPDATE and DELETE statements to have efficient and optimized queries for Joined data between 2 or more than 2 tables.

JOINS are one of the most basic used queries and anyone using or learning SQL based databases should have a thorough understanding of SQL Joins.