Join Queries
Aim:-To Execute Joins in MySQL
Theory: MySQL JOINS are used with SELECT statement. It is used to retrieve data from multiple tables. It is performed 
whenever you need to fetch records from two or more tables.
There are three types of MySQL joins:
MySQL INNER JOIN (or sometimes called simple join)
MySQL LEFT OUTER JOIN (or sometimes called LEFT JOIN)
MySQL RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
MySQL Inner JOIN (Simple Join): The MySQL INNER JOIN is used to return all rows from multiple tables where the
join condition is satisfied. It is the most common type of join.
QUERY:-
CREATE TABLE teacher(t_id int primary key,
name varchar(50) not null, qualification varchar(50) not null,salary int not null
);
INSERT INTO teacher VALUES (1,'Akshay','MCS',12000),
(2,'Amit','MBA',14000),
(3,'Aditya','MSC',13000),
(4,'Akshat','BSIT',15000),
(5,'Rahul','MPHIL',16000);
CREATE TABLE student(s_id int primary key,
name varchar(50) not null,class int not null,
t_id int not null
);
INSERT INTO studentVALUES (1,'Noman',11,2),
(2,'Asghar',12,4),
(3,'Furqan',10,2),
(4,'Khurram',11,1),
(5,'Asad',12,5),
(6,'Anees',10,1),
(7,'Khalid',11,2);
-- INNER JOIN QUERY--
SELECT t.t_id,t.name,t.qualification,s.name,s.classFROM teacher t
INNER JOIN student s ON t.t_id= s.t_id ORDER BY t_id,t.name;
34


MySQL Left Outer Join:The LEFT OUTER JOIN returns all rows from the left hand table specified in the ON condition
and only those rows from the other table where the join condition is fulfilled.
QUERY:-
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');
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,'Ram Kumar',45,19,"M",1),
(2,'Sarita Kumari',55,22,"M",2),
(3,'Salman Khan',62,20,"M",1),
(4,'Juhi Chawla',41,18,"M",3),
(5,'Anil Kaapoor',74,22,"M",1),
(6,'John Abraham',64,21,"M",2),
(7,'Shahid Kapoor',52,20,"M",1);
SELECT * FROM personalLEFT JOIN city
ON personal.city=city.cid;
35
MySQL Right Outer Join: The MySQL Right Outer Join returns all rows from the RIGHT-hand table specifiedin the ON 
condition and only those rows from the other table where he join condition is fulfilled.
SQL FULL OUTER JOIN Keyword:The FULL OUTER JOIN keyword returns all records when there is a match in left
(table1) or right (table2) table records.
QUERY:-
SELECT * FROM personalLEFT JOIN city
ON personal.city=city.cid UNIONSELECT * FROM personal RIGHT JOIN city
ON personal.city=city.cid;
SQL CROSS JOIN Keyword: The CROSS JOIN keyword returns all records from both tables (table1 and table2).
QUERY:
SELECT * FROM studentCROSS JOIN City;
RESULT: The program for join queries in RDBMS are implemented successfully and output is verifie