Introduction to MYSQL joins

What is MYSQL join

In simple language , Joins are used in an MySql statement to query data from two or more tables, based on a relationship between certain columns in these tables.

Types of join

- Inner join
- Left join
- Right join

Inner join

Inner joins is used to retrive common records from tables(records which are present in both the tables) base on some unique id.

BASIC SYNTAX :-

		
select a.columnname1,b.columnname2 from table_name as a   
join table_name_2 as b  
on a.someuniqueid=b.someuniqueid ;

LETS TRY WITH EXAMPLE :-

lets create a table for employee

create table tbl_employee_master(
empid int(11) not null ,
emp_name varchar(255) not null ,
joining_date date not null ,
primary key idx_empid(empid)
) ;

create a table for employee department

create table tbl_employee_deartment(
empid int(11) not null ,
emp_department varchar(255) not null ,
primary key idx_empid(empid)
) ;

insert some data

insert into tbl_employee_master(empid,emp_name,joining_date) values(95253,'first_employee','2018-03-29') ,(95254,'second_employee','2018-03-29') insert into tbl_employee_deartment(empid,emp_department) values(95253,'database')

Retrive all employess along with their allocated department

	
	select a.*,b.emp_department from tbl_employee_master as a
	join tbl_employee_deartment as b
	on a.empid=b.empid ;
	

Left join

Left joins is used to retrive all the records from left table and matched record from right table ,If there is no match from right table it will show null values.

BASIC SYNTAX :-

		
select a.*,b.* from table_name as a   
left join table_name_2 as b  
on a.someuniqueid=b.someuniqueid ;	


select a.*,b.emp_department from tbl_employee_master as a
left join tbl_employee_deartment as b
on a.empid=b.empid ;

Above join will list all the records from tbl_employee_master along with their match(if any) with tbl_employee_deartment.

If you want only those records which are present in left table but not in right one then

		
select a.*,b.emp_department from tbl_employee_master as a
left join tbl_employee_deartment as b
on a.empid=b.empid 
where b.emp_department is null ;

Right join

Right joins is used to retrive all the records from right table and matched record from left table ,If there is no match from left table it will show null values.

BASIC SYNTAX :-

		
select a.*,b.* from table_name as a   
right join table_name_2 as b  
on a.someuniqueid=b.someuniqueid ;	

Lets insert something in tbl_employee_deartment table

insert into tbl_employee_deartment(empid,emp_department)
values(95255,'audit') ;

		
select b.*,a.emp_name from tbl_employee_master as a
right join tbl_employee_deartment as b
on a.empid=b.empid ;

Above join will list all the records from tbl_employee_deartment along with their match(if any) with tbl_employee_master.

If you want only those records which are present in right table but not in left one then

		
select b.*,a.emp_name from tbl_employee_master as a
right join tbl_employee_deartment as b
on a.empid=b.empid 
where a.emp_name is null ;

Support us by sharing our content :-

Facebook

LinkedIn

Support us on Patreon :-

Become Patron