MySQL INNER JOIN

Types of Join

What is Inner Join?

 How to work Inner Join?

Inner 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);

INNER JOIN

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

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

SELECT p.id,p.name,p.percentage,p.age,p.gender,c.cityname    // Hide city & city Id 
FROM personal p INNER JOIN city c
ON p.city = c.cid;

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

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

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