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;


Post a Comment

0 Comments