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
- Find out details of customers who have ordered something
To solve the above query we can use the concept of inner join
- 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.
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
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?
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
- 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 |
+—–+———————+——-+—-
- 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;
For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!
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 |
Do watch our Channel to learn more: Click Here
Author:-
Pooja Nandode-Bhavsar
Call the Trainer and Book your free demo class for SQL now!!!
© Copyright 2020 | SevenMentor Pvt Ltd.