# Outer Joins In MYSQL

• By Pooja Nandode-Bhavsar
• August 3, 2023
• SQL

# Outer Joins In MYSQL

In the last blog, we talked about inner join in Mysql. Now, in this blog, we will discuss Outer joins in MYSQL, Self join, Cross join, and Full join.

Basically, there are two types of Outer joins that we have in Mysql i.e. left outer join and right outer join.

## 1. Left Outer Join

In the case of the left outer join will get all the records from the left table and the matching records from the right table.

Now, let’s Consider below to tables:

• Cust table

cid | cname | cemail             |

+—–+——-+——————–+

|   1 | ram   | ram123@gamil.com   |

|   2 | sham  | sham123@gamil.com  |

|   3 | gita  | gita23@gamil.com   |

|   4 | riya  | riya123@gamil.com  |

|   5 | priya | priya123@gamil.com

• Order1 table

oid | o_date              | o_amt | cid  |

+—–+———————+——-+——+

|   2 | 2023-02-05 00:00:00 |    14 |    2 |

|   5 | 2023-01-26 00:00:00 |    64 |    1 |

|   8 | 2022-03-18 00:00:00 |    32 |    2 |

|  10 | 2022-03-26 00:00:00 |    36 |    4 |

|  11 | 2022-06-17 00:00:00 |    32 |    3 |

|  12 | 2022-08-25 00:00:00 |    20 |    4 |

|  13 | NULL                |    75 |    3 |

|  88 | 2022-07-15 00:00:00 |    32 | NULL |

|  89 | 2022-08-25 00:00:00 |    20 |    3

1. Find out details of customers who have ordered something

To solve the above query we can use the concept of inner join

1. find out details of ALL customers who have ordered or not=> left join

To solve the above query we can use the concept of left outer join

select * from cust LEFT join order1 on cust.cid=order1.cid;

#getting specific columns

select cust.cid,cname,oid,o_amt from cust LEFT join order1 on cust.cid=order1.cid;

Note: Are you looking to advance your career in data analysis or database management? Look no further than SQL training in Pune!

In the above query, cust is the left table and order1 is the right table so in the output table, we will get all the records of the Cust table and matching records of the right table. So the output will look like this,

select * from cust LEFT join order1 on cust.cid=order1.cid;

+—–+——-+——————–+——+———————+——-+——+

| cid | cname | cemail             | oid  | o_date              | o_amt | cid  |

+—–+——-+——————–+——+———————+——-+——+

|   1 | ram   | ram123@gamil.com   |    5 | 2023-01-26 00:00:00 |    64 |    1 |

|   2 | sham  | sham123@gamil.com  |    2 | 2023-02-05 00:00:00 |    14 |    2 |

|   2 | sham  | sham123@gamil.com  |    8 | 2022-03-18 00:00:00 |    32 |    2 |

|   3 | gita  | gita23@gamil.com   |   11 | 2022-06-17 00:00:00 |    32 |    3 |

|   3 | gita  | gita23@gamil.com   |   13 | NULL                |    75 |    3 |

|   3 | gita  | gita23@gamil.com   |   89 | 2022-08-25 00:00:00 |    20 |    3 |

|   4 | riya  | riya123@gamil.com  |   10 | 2022-03-26 00:00:00 |    36 |    4 |

|   4 | riya  | riya123@gamil.com  |   12 | 2022-08-25 00:00:00 |    20 |    4 |

|   5 | priya | priya123@gamil.com | NULL | NULL                |  NULL | NULL |

+—–+——-+——————–+——+———————+——-+——+

In the above output table we are getting the record of cust “priya” also even if she has ordered anything.

## 2. Right Outer Join

In the case of the right outer join will get all the records from the right table and the matching records from the left table.

Q.Findout order details of all customers

select * from cust right join order1 on cust.cid=order1.cid;

#getting specific columns

select cust.cid,cname,oid,o_amt from cust right join order1 on cust.cid=order1.cid;

In the above query, cust is the left table and order1 is the right table so in the resultant output will get all the records of the right table.

As the right table contains a foreign key column that holds the values of the primary key column of the left table so the values that we have in the right table are from the left table only so in the inner join also will get all the records of the right table(here,oreder1 is a right table) so why we should use the right join in our query?

1. Why do we need to have the right join in SQL?

If we need to perform a join between 3 tables in a single query and with 2 tables using left join and we won’t get all records from the 3rd table then we can’t use left join with the 3rd table,

so we have to use the right join with 3rd table

t1  left  t2 left t3

t1  left  t2 right t3

## Self Join

Self-join is a type of join where a table joins with itself. This means in case of self join we need to create a copy/alias of the same table and we can perform self join within that 2 alias of a table. Basically, we should go with self-join whenever we need to compare a single record of a table with the remaining all the records of same table.

For an example,let’s consider the following query that belongs to the order1 table

Order1 table:

| oid | o_date              | o_amt | cid  |

+—–+———————+——-+——+

|   2 | 2023-02-05 00:00:00 |    14 |    2 |

|   5 | 2023-01-26 00:00:00 |    64 |    1 |

|   8 | 2022-03-18 00:00:00 |    32 |    2 |

|  10 | 2022-03-26 00:00:00 |    36 |    4 |

|  11 | 2022-06-17 00:00:00 |    32 |    3 |

|  12 | 2022-08-25 00:00:00 |    20 |    4 |

|  13 | NULL                |    75 |    3 |

|  88 | 2022-07-15 00:00:00 |    32 | NULL |

|  89 | 2022-08-25 00:00:00 |    20 |    3 |

+—–+———————+——-+—-

1. Find out customers who have ordered more than once

So, in the above, we need to find out those customer details who have order1 more than one time. This means in this query we need to count the customer id in the order1 table if the cust id is founded to be present more than once in the cid column of the order1 table it means that the cust has ordered more than one time. Join our SQL Course in Pune today and take your first step towards becoming a skilled SQL professional! So to find out this we need to compare the cust id of one record with the remaining records and if that same id is founded in another record it means that the cust has ordered more than once. Please refer below query:

select * from order1 as o1 join order1 as o2 on o1.cid=o2.cid AND o1.oid != o2.oid;

## Cross Join

In the case of cross-join, each record of the first table should be multiplied by all the records of the second table. means, in short, we in the case cross join cross  product will be performed between both tables and we can calculate by the formula:

I.e total no of records of a first table * total no of records of the second table

The query of cross-join is given below:

select * from cust cross join order1;

+—–+——-+——————–+—–+———————+——-+——+

| cid | cname | cemail             | oid | o_date              | o_amt | cid  |

+—–+——-+——————–+—–+———————+——-+——+

|   5 | priya | priya123@gamil.com |   2 | 2023-02-05 00:00:00 |    14 |    2 |

|   4 | riya  | riya123@gamil.com  |   2 | 2023-02-05 00:00:00 |    14 |    2 |

|   3 | gita  | gita23@gamil.com   |   2 | 2023-02-05 00:00:00 |    14 |    2 |

|   2 | sham  | sham123@gamil.com  |   2 | 2023-02-05 00:00:00 |    14 |    2 |

|   1 | ram   | ram123@gamil.com   |   2 | 2023-02-05 00:00:00 |    14 |    2 |

|   5 | priya | priya123@gamil.com |   5 | 2023-01-26 00:00:00 |    64 |    1 |

|   4 | riya  | riya123@gamil.com  |   5 | 2023-01-26 00:00:00 |    64 |    1 |

|   3 | gita  | gita23@gamil.com   |   5 | 2023-01-26 00:00:00 |    64 |    1 |

|   2 | sham  | sham123@gamil.com  |   5 | 2023-01-26 00:00:00 |    64 |    1 |

|   1 | ram   | ram123@gamil.com   |   5 | 2023-01-26 00:00:00 |    64 |    1 |

|   5 | priya | priya123@gamil.com |   8 | 2022-03-18 00:00:00 |    32 |    2 |

|   4 | riya  | riya123@gamil.com  |   8 | 2022-03-18 00:00:00 |    32 |    2 |

|   3 | gita  | gita23@gamil.com   |   8 | 2022-03-18 00:00:00 |    32 |    2 |

|   2 | sham  | sham123@gamil.com  |   8 | 2022-03-18 00:00:00 |    32 |    2 |

|   1 | ram   | ram123@gamil.com   |   8 | 2022-03-18 00:00:00 |    32 |    2 |

|   5 | priya | priya123@gamil.com |  10 | 2022-03-26 00:00:00 |    36 |    4 |

|   4 | riya  | riya123@gamil.com  |  10 | 2022-03-26 00:00:00 |    36 |    4 |

|   3 | gita  | gita23@gamil.com   |  10 | 2022-03-26 00:00:00 |    36 |    4 |

|   2 | sham  | sham123@gamil.com  |  10 | 2022-03-26 00:00:00 |    36 |    4 |

|   1 | ram   | ram123@gamil.com   |  10 | 2022-03-26 00:00:00 |    36 |    4 |

|   5 | priya | priya123@gamil.com |  11 | 2022-06-17 00:00:00 |    32 |    3 |

|   4 | riya  | riya123@gamil.com  |  11 | 2022-06-17 00:00:00 |    32 |    3 |

|   3 | gita  | gita23@gamil.com   |  11 | 2022-06-17 00:00:00 |    32 |    3 |

|   2 | sham  | sham123@gamil.com  |  11 | 2022-06-17 00:00:00 |    32 |    3 |

|   1 | ram   | ram123@gamil.com   |  11 | 2022-06-17 00:00:00 |    32 |    3 |

|   5 | priya | priya123@gamil.com |  12 | 2022-08-25 00:00:00 |    20 |    4 |

|   4 | riya  | riya123@gamil.com  |  12 | 2022-08-25 00:00:00 |    20 |    4 |

|   3 | gita  | gita23@gamil.com   |  12 | 2022-08-25 00:00:00 |    20 |    4 |

|   2 | sham  | sham123@gamil.com  |  12 | 2022-08-25 00:00:00 |    20 |    4 |

|   1 | ram   | ram123@gamil.com   |  12 | 2022-08-25 00:00:00 |    20 |    4 |

|   5 | priya | priya123@gamil.com |  13 | NULL                |    75 |    3 |

|   4 | riya  | riya123@gamil.com  |  13 | NULL                |    75 |    3 |

|   3 | gita  | gita23@gamil.com   |  13 | NULL                |    75 |    3 |

|   2 | sham  | sham123@gamil.com  |  13 | NULL                |    75 |    3 |

|   1 | ram   | ram123@gamil.com   |  13 | NULL                |    75 |    3 |

|   5 | priya | priya123@gamil.com |  88 | 2022-07-15 00:00:00 |    32 | NULL |

|   4 | riya  | riya123@gamil.com  |  88 | 2022-07-15 00:00:00 |    32 | NULL |

|   3 | gita  | gita23@gamil.com   |  88 | 2022-07-15 00:00:00 |    32 | NULL |

|   2 | sham  | sham123@gamil.com  |  88 | 2022-07-15 00:00:00 |    32 | NULL |

|   1 | ram   | ram123@gamil.com   |  88 | 2022-07-15 00:00:00 |    32 | NULL |

|   5 | priya | priya123@gamil.com |  89 | 2022-08-25 00:00:00 |    20 |    3 |

|   4 | riya  | riya123@gamil.com  |  89 | 2022-08-25 00:00:00 |    20 |    3 |

|   3 | gita  | gita23@gamil.com   |  89 | 2022-08-25 00:00:00 |    20 |    3 |

|   2 | sham  | sham123@gamil.com  |  89 | 2022-08-25 00:00:00 |    20 |    3 |

|   1 | ram   | ram123@gamil.com   |  89 | 2022-08-25 00:00:00 |    20 |    3 |