SQL Joins with Example

In this blog, I will talk about SQL Joins with Example. Basically, joins are used to join two or more tables to show common records between both tables based on related columns.  A foreign key is also used to link or join two tables but it does not combine or access common columns hence we need to use SQL joins concept

There are 5 types of joins in MySql

1.     Inner join(regular join)
2.     Left outer join
3.     Right outer join
4.     Self join
5.     Cross join
6.     Full Outer Join

For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!

1. Inner Join

Inner Join is used to fetch common or matching records from both tables based on common columns.

Here, we need to create 2 tables

 

1.Customer

create table cust

(

cid int primary key,

cname varchar(50),

cemail varchar(50)

);

 

2.Order

create table order1

(

 oid int not null,

 o_date date,

 o_amt int,

 cid int,

 primary key(oid),

 foreign key(cid) references cust(cid)

 );

 

So, in the above tables cid(foreign key) column of the order1 table can point to the cid(primary key) column of the cust table.

 

After inserting records in both tables. The tables look like given below:

 

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@gmail.com |

 

Order table:

 

oid | o_date | o_amt | cid  |

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

|   2 | 2023-02-01 | 14 |    2 |

|   4 | 2023-01-13 | 24 |    3 |

|   5 | 2023-01-22 | 64 |    1 |

|   8 | 2022-03-14 | 28 |    2 |

|   9 | 2022-03-17 | 39 |    4 |

|  10 | 2022-03-22 | 36 |    4 |

|  11 | 2023-03-22 | 66 |    4 |

For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!

Let’s See some queries on Inner Join:

 

1. find the the details of only those customers who have order something without join and using the where clause

 

select * from cust,order1 where  cust.cid=order1. cid;

 

with inner join

select * from cust JOIN order1 ON  cust.cid=order1.cid;

 

the above queries will fetch common records between both the tables

 

Note: Enhance your career prospects and gain in-demand skills. Enroll now in SQL Training in Pune and become a SQL master!

 

using using alias command

 

select * from cust as c JOIN order1 as o ON  c.cid=o. cid; 

 

 

getting specific column in inner join

 

select c.cid,cname,oid,o_amt from cust as c JOIN order1 as o ON  c.cid=o. cid;

 

•         Inner Join With Aggregate Function

 

1. Find out total order amount of customers

 

 select sum(o_amt) from cust  as c  JOIN order1 as o ON  c.cid=o.cid;

 

1. Find out the customers who have ordered earlier according to date

 

select min(o_date) from cust  as c  JOIN order1 as o ON  c.cid=o.cid;

 

  

 

•         Inner  Join With Logical Operators

 

1. find out total order amount of only those customers who have ordered on the date “2022-04-22” or on the date “2022-06-13”

 

  select sum(o_amt) from cust  as c  JOIN order1 as o ON  c.cid=o.cid

  where o_date=”2022-04-22″ or o_date=”2022-06-13″;

 

1. find out customer details whose order amount is greater than 30 and are order on the date ” 2023-01-22″

 

select * from cust as c JOIN order1 as o ON  c.cid=o.cid   where o.o_amt>30 && o_date= “2023-01-22”;

 

•         Inner  Join With Like Operator

 

1. findout highest order amount customers whose names start with “r” or on the date “2022-06-13”

 

  select max(o_amt) from cust  as c  JOIN order1 as o ON  c.cid=o.cid

  where cname like “r%”;

 

1. find out customers who have ordered in the year of 2022(starts with)

 

select * from cust as c JOIN order1 as o ON  c.cid=o.cid  where o_date like “2022%”;

 

•         Inner  Join With Order by clause

 

Q.find out customer details in the descending order of thier order amount

 

select * from cust as c JOIN order1 as o ON  c.cid=o.cid order by  o_amt desc;

 

For Free, Demo classes Call: 020 7117 1500
Registration Link: Click Here!

 

•         Inner  Join With Distinct clause

 

 

1. find out unique customers who have ordered after date 2022-06-15

 

select distinct(o.cid),cname from cust as c JOIN order1 as o ON  c.cid=o.cid

where o_date > “2022-06-15”;

 

Note: Unlock the power of data manipulation with our SQL classes in Pune. Learn the fundamentals, query optimization, and database management from industry experts.

 

•         Inner  Join With between Operator

 

1. find out customer details whose order amount is in between 30 to 50

 

select * from cust as c JOIN order1 as o ON  c.cid=o.cid where o_amt between 30 AND 50;

 

Inner join with 3 tables

 

creating third table  i.e  supplier

 

create table supplier

(

   sid int primary key,

   sname varchar(45),

   cid int,

   oid  int,

   foreign key(cid) references cust(cid),

   foreign key(oid) references order1(oid)    

);

 

In the above table we are using two foreign keys. cid foreign key points to the primary key column of “cust” table and oid foreign key points to the order1 table

 

After inserting records into Supplier table it look like given below:

 

sid | sname  | cid  | oid  |

+—–+————+——+——+

|   1 | amazon |    2 | 5 |

|   2 | flipkart   | 4 |   10 |

|   3 | myntra |    2 | 8 |

|   4 | big basket | 3 |   11 |

 

Do visit our channel to know more: Click Here

 

1. Findout order and supplier details of those customers who have order something

 

select * from cust

inner join order1

on

  cust.cid=order1.cid

inner join supplier

on

   cust.cid=supplier.cid;

 

Author:-

Pooja Nandode-Bhavsar
Call the Trainer and Book your free demo class for SQL now!!!

© Copyright 2020 | SevenMentor Pvt Ltd.