MySQL EXISTS

MySQL Tutorial

MySQL-Home MySQL-Environment setup MySQL- Workbench MySQL-Basic syntax MySQL-Operator MySQL-Data type MySQL-Comments MySQL-Create DB MySQL-Drop DB MySQL-Select DB MySQL-Create Table MySQL-Drop table MySQL-Truncate MySQL-Primary Key MySQL-Foreign Key MySQL-Null MySQL-Increment MySQL-Having MySQL-Top MySQL-Insert Statement MySQL-Select Statement MySQL-Alter Statement MySQL-Where MySQL-And & Or MySQL-Default values MySQL-Exists MySQL-Order by MySQL-View MySQL-Update Statement MySQL-Delete Statement MySQL-Like MySQL-Sort MySQL-Limit MySQL-Min MySQL-Max MySQL-Group MySQL-In MySQL-Between MySQL-Union MySQL-Count MySQL-Average MySQL-Sum MySQL-Date & Time MySQL-Import MySQL-Export MySQL-Index MySQL-Temporary MySQL-Join MySQL-Full Join MySQL-Inner Join MySQL-Left Join MySQL-Right Join MySQL-Store Procedure MySQL-Injection MySQL-PHP connection

« Previous Next »

The EXISTS operator is used to test for the existence of any record in a sub query.

The EXISTS operator returns true if the sub query returns one or more records.

EXISTS is commonly used with correlated sub queries.


SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

Let's put these statements into real use.

We've a table named Students and Teachers in our database that contains the following records:

TECH_NO	NAME	SUBJECT	ROLL_NO
20	Kayla	PHP	6
21	Albert	Java	2
21	Albert	Java	5
22	Helen	Node.js	3
23	Anne	Python	2
24	Jaime	C++	1

The following MySQL statement returns TRUE and lists the Teacher name with a Student ROLL_NO less than 4:


SELECT TECH_NO
FROM Teachers
WHERE EXISTS 
(SELECT ROLL_NO FROM Students 
WHERE Students.ROLL_NO = Teachers.ROLL_NO AND ROLL_NO < 4);

Now, after executing the above MySQL statement,

« Previous Next »

MySQL - EXISTS