MySQL SubQuery With EXISTS & NOT EXISTS

 How to use SubQuery in MySql table:

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

use SubQuery

SELECT name FROM personal
WHERE courses = (SELECT course_id FROM courses WHERE course_name = "MBA");

SELECT course_id FROM courses WHERE course_name = "MBA";

SELECT name FROM personal
WHERE courses IN (SELECT course_id FROM courses WHERE course_name IN ("MBA","Btech"));

How to use Exists command in mysql:

Syntax:

How to use NOt Exists command in mysql:

Syntax:

Code:

Exists & Not Exists
SELECT name FROM personal
WHERE EXISTS (SELECT course_id FROM courses WHERE course_name IN ("MBA"));

SELECT name FROM personal
WHERE EXISTS (SELECT course_id FROM courses WHERE course_name IN ("Mtech"));

SELECT name FROM personal
WHERE NOT EXISTS (SELECT course_id FROM courses WHERE course_name IN ("Mtech"));

SELECT name FROM personal
WHERE NOT EXISTS (SELECT course_id FROM courses WHERE course_name IN ("MBA"));

SQL