/* 024 - LEFT JOIN & RIGHT JOIN */
-- create table "tOne" and insert records
CREATE TABLE tOne(
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 tOne(id,name,percentage,age,gender,city)
VALUES
(1,"Ram Kumar","45","13","M",1),
(2,"Sarita Kumari","56","21","F",2),
(3,"Salman Khan","62","20","M",1),
(4,"Juhi Chawla","47","18","F",3),
(5,"Anil Kapoor","74","22","M",1),
(6,"John Abraham","64","21","M",2),
(7,"Shahid Kapoor","52","20","M",1);
-- create table "city" and insert records
CREATE TABLE city(
cid INT NOT NULL AUTO_INCREMENT,
cityname VARCHAR(50) NOT NULL,
PRIMARY KEY (cid)
);
INSERT INTO city(cityname)
VALUES('Agra'),
('Delhi'),
('Bhopal'),
('Jaipur'),
('Noida');
--LEFT JOIN (show all first table with comman values form both tables)
SELECT * FROM tOne LEFT JOIN city
ON tOne.city = city.cid;
SELECT * FROM tOne p LEFT JOIN city c -- using alias
ON p.city = c.cid;
SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname -- selected column
FROM tOne p LEFT JOIN city c
ON p.city = c.cid;
SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname -- by using keyword where
FROM tOne 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 -- by using order by
FROM tOne p LEFT JOIN city c
ON p.city = c.cid
WHERE gender = "M"
ORDER BY name;
--RIGHT JOIN ( show all second table value with comman values form both table )
SELECT * FROM tOne RIGHT JOIN city
ON tOne.city = city.cid;
SELECT * FROM tOne p RIGHT JOIN city c
ON p.city = c.cid;
SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname
FROM tOne p RIGHT JOIN city c
ON p.city = c.cid;