MY SQL INNER JOINS

  CREATE TABLE districts(

    cid INT NOT NULL AUTO_INCREMENT,

    district_name VARCHAR(50) NOT NULL,

    PRIMARY KEY (cid));

INSERT INTO districts(district_name)

VALUES('Agra'),

('Delhi'),

('Bhopal'),

('Jaipur'),

('Noida'),

('Lucknow'),

('ballia');

select * from districts 


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

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


                 SYNTAX FOR INNER JOINS

SELECT COLUMNS 

FROM TABLE1 

INNER JOIN TABLE2

ON TABLE1.COLUM_NAME = TABLE2.COLUM_NAME;

                               🠋                                      🠋                                 FOREIGN KEY               PRIMARY KEY

                               

SELECT * FROM citizens INNER JOIN districts

ON citizens.district_name = districts.cid;


   













     Here giving the alias name for simplicity

SELECT * FROM citizens c INNER JOIN districts d
ON c.district_name = d.cid;

output is the same as the previous out








Here we have printed the specific columns' name

SELECT c.id,c.name,c.percentage_of_population,c.age,c.gender,d.district_name
FROM citizens C INNER JOIN districts d
ON c.district_name = d.cid;










      Here we are using the where clause 
SELECT c.id,c.name,c.percentage_of_population,c.age,c.gender,d.district_name
FROM citizens C INNER JOIN districts d
ON c.district_name = d.cid
WHERE d.district_name = "Delhi";






SELECT c.id,c.name,c.percentage_of_population,c.age,c.gender,d.district_name
FROM citizens C INNER JOIN districts d
ON c.district_name = d.cid
WHERE d.district_name = "Delhi"
ORDER BY c.name;





NOTE by the help of inner joins we print the intersection of columns present in both tables.














Post a Comment

0 Comments