MySQL UNION & UNION ALL

 How to work UNION & UNION ALL:

Example:

Syntax:

Code:

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,

    courses INT NOT NULL,

    PRIMARY KEY (id),

FOREIGN KEY (city) REFERENCES City (cid),

    FOREIGN KEY (courses) REFERENCES Courses (course_id)  

);

INSERT INTO personal(id,name,percentage,age,gender,city,courses)

VALUES

(1,"Aqib","45","13","M",1,1),

(2,"Khadija","56","21","F",2,2),

(3,"Salman Khan","62","20","M",1,1),

(4,"Aqeela","47","18","F",3,1),

(5,"Saqib","74","22","M",1,3),

(6,"Abraham","64","21","M",2,2),

(7,"Shahid","52","20","M",1,3);

create table "city" and insert records

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 "courses" and insert records
CREATE TABLE courses(
    course_id INT NOT NULL AUTO_INCREMENT,
    course_name VARCHAR(50) NOT NULL,
    PRIMARY KEY (course_id)
);

INSERT INTO courses(course_name)
VALUES('Btech'),
('BCA'),
('MBA');

create table "lecturers" and insert records

CREATE TABLE lecturers(
	id INT NOT NULL,
	name VARCHAR(50) NOT NULL,
	age INT NOT NULL,
	gender VARCHAR(1) NOT NULL,
	city INT NOT NULL,
    courses INT NOT NULL,
    PRIMARY KEY (id),
	FOREIGN KEY (city) REFERENCES City (cid),
    FOREIGN KEY (courses) REFERENCES Courses (course_id)  
);

INSERT INTO lecturers(id,name,age,gender,city,courses)
VALUES
(1,"Rana","37","M",1,2),
(2,"Aleena","39","F",4,3),
(3,"Aqib","38","M",2,1),
(4, "Khan","45","M",3,2),
(5,"Nagma","42","F",2,1);

SQL

UNION & UNION ALL

SELECT * FROM students
UNION
SELECT * FROM lecturers;

SELECT name FROM students
UNION
SELECT name FROM lecturers;

SELECT name FROM students
UNION ALL
SELECT name FROM lecturers;

SELECT name,age FROM students
UNION ALL
SELECT name,age FROM lecturers;


SELECT name,age FROM students WHERE gender ="M"
UNION ALL
SELECT name,age FROM lecturers WHERE gender ="M";

SELECT name,age FROM students WHERE gender ="M"
UNION ALL
SELECT name,age FROM lecturers WHERE gender ="F";


SELECT name,age FROM students WHERE city =2
UNION ALL
SELECT name,age FROM lecturers WHERE city = 2;



SELECT name,age FROM students
WHERE city = (SELECT cid FROM city WHERE cityname = "Lhr")
UNION ALL
SELECT name,age FROM lecturers
WHERE city = (SELECT cid FROM city WHERE cityname = "Mul");


SELECT s.name,s.age, c.cityname FROM students s
INNER JOIN city c ON s.city = c.cid
WHERE c.cityname = "Mul"
UNION ALL
SELECT l.name,l.age,ci.cityname FROM lecturers l
INNER JOIN city ci ON l.city = ci.cid
WHERE ci.cityname = "Mul";