MySQL LEFT JOIN & RIGHT JOIN

 Types of Join In MySql:

How to work Left Join:

Works:

Left Join Syntax:

Code:
CREATE TABLE city(

cid INT NOT NULL AUTO_INCREMENT,

cityname VARCHAR(50) NOT NULL,

PRIMARY KEY (cid)

);

insert records in "city" table

INSERT INTO city(cityname)

VALUES('Mul'),

('Lhr'),

('Mul'),

('Bhw'),

('Mul');

create table "personal" and insert records

CREATE TABLE personal(

id INT NOT NULL,

name VARCHAR(50) NOT NULL,

percentage INT NOT NULL,

age INT NOT NULL,

gender VARCHAR(1) NOT NULL,

city INT NOT NULL,

PRIMARY KEY (id),

FOREIGN KEY (city) REFERENCES City (cid)

);

INSERT INTO personal(id,name,percentage,age,gender,city)

VALUES

(1,"Aqib","45","13","M",1),

(2,"Sheikh Anum","56","21","F",2),

(3,"Salman Khan","62","20","M",1),

(4,"Aqeela","47","18","F",3),

(5,"Ali","74","22","M",1),

(6,"Hamza","64","21","M",2),

(7,"Shahid","52","20","M",1);

LEFT JOIN

SELECT * FROM personal LEFT JOIN city
ON personal.city = city.cid;

SELECT * FROM personal INNER JOIN city
ON personal.city = city.cid;

SELECT * FROM personal p LEFT JOIN city c
ON p.city = c.cid;

SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname
FROM personal p LEFT JOIN city c
ON p.city = c.cid;

SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname
FROM personal p LEFT JOIN city c
ON p.city = c.cid
WHERE gender = "M";

SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname
FROM personal p LEFT JOIN city c
ON p.city = c.cid
WHERE gender = "M"
ORDER BY name;

How to work Right Join:

Works:

Right Join Syntax:

RIGHT JOIN

SELECT * FROM personal RIGHT JOIN city
ON personal.city = city.cid;

SELECT * FROM personal p RIGHT JOIN city c
ON p.city = c.cid;

SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname
FROM personal p RIGHT JOIN city c
ON p.city = c.cid;