MYSQL LEFT JOIN AND RIGHT JOIN

                                         Creating a table 1 

 CREATE TABLE districts(

    cid INT NOT NULL AUTO_INCREMENT,

    district_name VARCHAR(50) NOT NULL,

    PRIMARY KEY (cid)

);

Inerting the value in table 1

INSERT INTO districts(district_name)

VALUES('Agra'),

('Delhi'),

('Bhopal'),

('Jaipur'),

('Noida'),

('lucknow'),

('ballia');

select * from districts 

                          Creating a table 2

CREATE TABLE citizens(

id INT NOT NULL,

name VARCHAR(50) NOT NULL,

percentage_of_population INT NOT NULL,

age INT NOT NULL,

gender VARCHAR(1) NOT NULL,

district_name INT NOT NULL,

PRIMARY KEY (id),

    foreign key (district_name) REFERENCES districts(cid)

    );

    select * from citizens

Inerting the value in table 2

INSERT INTO citizens(id,name,percentage_of_population,age,gender,district_name)

VALUES

(1,"Ramesh Kumar","45","13","M",1),

(2,"Saroja Kumari","56","21","F",2),

(3,"sarsvari devi ","62","20","M",1),

(4,"Juhi kumari","47","18","F",3),

(5,"Anil yadav","74","22","M",1),

(6,"Joy Shanker","64","21","M",2),

(7,"sunil yadav ","52","20","M",1);

select * from citizens

select * from districts

                               LEFT JOIN

SELECT * FROM citizens LEFT JOIN districts

ON citizens.district_name = districts.cid;

SELECT * FROM citizens c LEFT JOIN districts d

ON c.district_name = d.cid;

SELECT c.id,c.name,c.percentage_of_population,c.age,c.gender,d.district_name

FROM citizens C LEFT JOIN districts d

ON c.district_name = d.cid

SELECT c.id,c.name,c.percentage_of_population,c.age,c.gender,d.district_name

FROM citizens C LEFT JOIN districts d

ON c.district_name = d.cid

WHERE d.district_name = "Delhi"

ORDER BY c.name;

SELECT c.id,c.name,c.percentage_of_population,c.age,c.gender,d.district_name

FROM citizens C LEFT JOIN districts d

ON c.district_name = d.cid

WHERE GENDER = "M"

SELECT c.id,c.name,c.percentage_of_population,c.age,c.gender,d.district_name

FROM citizens C LEFT JOIN districts d

ON c.district_name = d.cid

WHERE GENDER = "M"

ORDER BY name;

                              RIGHT JOIN

SELECT * FROM citizens RIGHT JOIN districts

ON citizens.district_name = districts.cid;  

SELECT * FROM citizens c RIGHT JOIN districts d

ON c.district_name = d.cid;   

SELECT c.id,c.name,c.percentage_of_population,c.age,c.gender,d.district_name

FROM citizens C RIGHT JOIN districts d

ON c.district_name = d.cid;  

SELECT c.id,c.name,c.percentage_of_population,c.age,c.gender,d.district_name

FROM citizens C RIGHT JOIN districts d

ON c.district_name = d.cid

WHERE GENDER = "M"

SELECT c.id,c.name,c.percentage_of_population,c.age,c.gender,d.district_name

FROM citizens C RIGHT JOIN districts d

ON c.district_name = d.cid

WHERE GENDER = "M"

ORDER BY name;