/* 023 - INNER JOIN ( RETURN THE DATA WHICH IS COMMAN IN BOTH TABLE ) */
-- SYNTAX :
SELECT table_1_columns
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
--CONCEPT : lest say we have two table
Table name = personal
id name city
1 Ram Kumar 1
2 Sarita Kumari 2
3 Salman Khan 1
4 Juhi Chawla 3
5 Anil Kapoor 1
6 John Abraham 2
7 Shahid Kapoor 1
--Note : city (foreign key)
Table name = city
cid cityname
1 Agra
2 Delhi
3 Bhopal
4 Jaipur
5 Noida
--Note : cid (reference key)
-- below code fetch all the data
SELECT *
FROM personal
INNER JOIN city
ON personal.city = city.cid;
id name city cid cityname
1 Ram Kumar 1 1 Agra
2 Sarita Kumari 2 2 Delhi
3 Salman Khan 1 1 Agra
4 Juhi Chawla 3 3 Bhopal
5 Anil Kapoor 1 1 Agra
6 John Abraham 2 2 Delhi
7 Shahid Kapoor 1 1 Agra
-- Note : 5 (noida ) is not comman in both table so it is not present in above table
-- must declare foreign and reference key first
-- alias ( above code )
SELECT *
FROM personal p
INNER JOIN city c
ON p.city = c.cid; ( foregin key = primary key )
id name city cid cityname
1 Ram Kumar 1 1 Agra
2 Sarita Kumari 2 2 Delhi
3 Salman Khan 1 1 Agra
4 Juhi Chawla 3 3 Bhopal
5 Anil Kapoor 1 1 Agra
6 John Abraham 2 2 Delhi
7 Shahid Kapoor 1 1 Agra
-- selected column ( above code )
SELECT p.id,p.name, c.cityname
FROM personal p
INNER JOIN city c
ON p.city = c.cid; ( foregin key = primary key )
id name percentage age gender cityname
1 Ram Kumar 45 13 M Agra
2 Sarita Kumari 56 21 F Delhi
3 Salman Khan 62 20 M Agra
4 Juhi Chawla 47 18 F Bhopal
5 Anil Kapoor 74 22 M Agra
6 John Abraham 64 21 M Delhi
7 Shahid Kapoor 52 20 M Agra
-- match both table unique key and only join comman key values to first table,
/*-- PRACTICAL EXAMPLE: 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');
SELECT * FROM tOne INNER JOIN city -- from = first-table to inner join = another-table
ON tOne.city = city.cid; -- match with first-table.columan-name = second-table.column-name
SELECT * FROM tOne p INNER JOIN city c -- can use alias for making the table name short
ON p.city = c.cid;
SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname -- multiple column select, insted of using =*
FROM tOne p INNER JOIN city c
ON p.city = c.cid;
SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname -- with where conditon
FROM tOne p INNER JOIN city c
ON p.city = c.cid
WHERE c.cityname = "Agra";
SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname -- using order by
FROM tOne p INNER JOIN city c
ON p.city = c.cid
WHERE c.cityname = "Agra"
ORDER BY p.name;
SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname -- can use keyword join instead of inner join
FROM tOne p JOIN city c
ON p.city = c.cid
WHERE c.cityname = "Agra"
ORDER BY p.name;