How to Effectively Use SQL Joins?

In MySQL, Joins are used to combine rows from two or more tables based on a related column. Joins are essential in relational databases to retrieve data across tables, allowing us to fetch related information in a single query. There are several types of joins, each serving different use cases depending on how you want to retrieve data from the tables. Learn how to effectively use SQL Joins? to combine data from multiple tables, optimize queries, and enhance database performance with practical examples.

Joins are particularly useful when tables are related via primary and foreign keys. By understanding and using joins effectively, you can maximize the potential of your data.

Types of MySQL Joins

1. INNER JOIN
2. LEFT JOIN (or LEFT OUTER JOIN)
3. RIGHT JOIN (or RIGHT OUTER JOIN)
4. FULL JOIN (or FULL OUTER JOIN)
5. CROSS JOIN
6. SELF JOIN

• INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables. It returns only the rows where there is a match in both tables. It is the default and the most commonly used JOIN operation.

Syntax : 

SELECT columns

FROM table1

INNER JOIN table2

ON table1.common_column = table2.common_column;

Example: Consider two tables:

• employees: with columns emp_id, first_name, last_name and dept_id,salary.
• departments: with columns dept_id and dept_name.

employees Table

emp_id	first_name	last_name	dept_id	salary
1	John	Doe	1	50000
2	Jane	Smith	2	60000
3	Michael	Johnson	1	45000
4	Emily	Davis	2	55000
5	Alex	Patel	NULL	40000
6	Sarah	Kim	4	52000

 

department Table

dept_id	dept_name
1	IT
2	Sales
3	HR
4	Marketing
5	Finance

Example. 

SELECT e.first_name, e.last_name, d.dept_name

FROM employees e

INNER JOIN departments d

ON e.dept_id = d.dept_id;

 

Output: 

first_name	last_name	dept_name
John	Doe	IT
Jane	Smith	Sales
Michael	Johnson	IT
Emily	Davis	Sales
Sarah	Kim	Marketing

 

2. LEFT JOIN (LEFT OUTER JOIN)

The LEFT JOIN returns all records from the left table (table1) and the matched records from the right table (table2). If there is no match, NULL values are returned for columns from the right table.

 

Syntax : 

SELECT columns

FROM table1

LEFT JOIN table2

ON table1.common_column = table2.common_column;

Example: 

SELECT e.first_name, e.last_name, d.dept_name

FROM employees e

LEFT JOIN departments d

ON e.dept_id = d.dept_id;

first_name	last_name	dept_name
John	Doe	IT
Jane	Smith	Sales
Michael	Johnson	IT
Emily	Davis	Sales
Alex	Patel	NULL
Sarah	Kim	Marketing

3. RIGHT JOIN (RIGHT OUTER JOIN)

The RIGHT JOIN returns all records from the right table (table 2) and the matched records from the left table (table 1). If there is no match, NULL values are returned for columns from the left table.

 

Syntax : 

SELECT column_name(s)

FROM table1

RIGHT JOIN table2

ON table1.column_name = table2.column_name;

Example : 

SELECT e.first_name, e.last_name, d.dept_name

FROM employees e

RIGHT JOIN departments d

ON e.dept_id = d.dept_id;

 

Output : 

first_name	last_name	dept_name
John	Doe	IT
Jane	Smith	Sales
Michael	Johnson	IT
Emily	Davis	Sales
Sarah	Kim	Marketing
NULL	NULL	HR
NULL	NULL	Finance

4. FULL JOIN (FULL OUTER JOIN)

The FULL JOIN returns all records when there is a match in either the left (table1) or right (table2) table. If there is no match, NULL values are returned for the unmatched columns. MySQL doesn’t directly support FULL JOIN, so you can achieve it by combining LEFT JOIN and RIGHT JOIN with a UNION.

Syntax : 

SELECT columns

FROM table1

LEFT JOIN table2

ON table1.common_column = table2.common_column

UNION

SELECT columns

FROM table1

RIGHT JOIN table2

ON table1.common_column = table2.common_column;

 

Example : 

SELECT e.emp_name, d.dept_name

FROM employees e

LEFT JOIN departments d

ON e.dept_id = d.dept_id

UNION

SELECT e.emp_name, d.dept_name

FROM employees e

RIGHT JOIN departments d

ON e.dept_id = d.dept_id;

 

Output:

first_name	last_name	dept_name
John	Doe	IT
Jane	Smith	Sales
Michael	Johnson	IT
Emily	Davis	Sales
Alex	Patel	NULL
Sarah	Kim	Marketing
NULL	NULL	HR

 

For Free, Demo classes Call: 020-71179559

Registration Link: Click Here!

5. CROSS JOIN

A CROSS JOIN, also known as a Cartesian product, is a join operation that combines every row from one table with every row from another table. This results in a new table that contains all possible combinations of rows from the two tables.

 

Syntax: 

SELECT columns

FROM table1

CROSS JOIN table2;

 

Example:

Consider the following employees and departments tables:

employees

emp_id	first_name	last_name
1	John	Doe
2	Jane	Smith

 

departments

dept_id	dept_name
1	IT
2	Sales

 

Example:

SELECT e.emp_id, e.first_name, e.last_name, d.dept_id, d.dept_name

FROM employees e

CROSS JOIN departments d;

 

Output

emp_id	first_name	last_name	dept_id	dept_name
1	John	Doe	1	IT
1	John	Doe	2	Sales
2	Jane	Smith	1	IT
2	Jane	Smith	2	Sales

 

6. SELF JOIN

A SELF JOIN is a join where a table is joined with itself. It’s useful when you want to compare rows within the same table.

Syntax : 

SELECT column_name(s)

FROM table_name AS alias1

INNER JOIN table_name AS alias2

ON alias1.column_name = alias2.column_name;

Example:

Let’s consider an employees table with the following structure:

employee_id	first_name	last_name	manager_id
1	John	Doe	2
2	Jane	Smith	NULL
3	Michael	Johnson	1
4	Emily	Davis	2

 

To find the name of each employee and their manager, we can use a self-join:

Example : 

SELECT e1.first_name AS Employee, e2.first_name AS Manager

FROM employees e1

INNER JOIN employees e2

ON e1.manager_id = e2.employee_id;

 

Output: 

Employee	Manager
John	Jane
Michael	John
Emily	Jane

 

Do watch our Channel to learn more: Click Here

 

Author:

Mahesh Kankrale

Call the Trainer and Book your free demo Class For SQL Call now!!!
| SevenMentor Pvt Ltd.

© Copyright 2021 | SevenMentor Pvt Ltd.