Mysql Exists

Mysql Exists return you only matches records of table 1 with table2. The unmatchable records are not displayed in the output of the table.

Understand with Example

The Tutorial illustrate an example that create a table 'MyTable' with required fieldnames and datatypes respectively.

Create Table MyTable:

mysql> CREATE TABLE MyTable (
    ->              Empid int(10),
    ->              Empname varchar(60)
    ->              Salary int(90)
    ->            );
Query OK, 0 rows affected (0.13 sec)

Insert Values into MyTable:

The insert into is used to add the records value to the table 'MyTable'.

mysql>   insert into MyTable values(01,'Girish','20000');
Query OK, 1 row affected (0.02 sec)

mysql>   insert into MyTable values(10,'Komal','20000');
Query OK, 1 row affected (0.01 sec)

mysql>     insert into MyTable values(02,'A','21000');
Query OK, 1 row affected (0.01 sec)

mysql>     insert into MyTable values(03,'C','22000');
Query OK, 1 row affected (0.00 sec)

mysql>     insert into MyTable values(04,'V','23000');
Query OK, 1 row affected (0.00 sec)

mysql>     insert into MyTable values(05,'B','24000');
Query OK, 1 row affected (0.00 sec)

mysql>     insert into MyTable values(06,'E','25000');
Query OK, 1 row affected (0.00 sec)

mysql>     insert into MyTable values(07,'Q','26000');
Query OK, 1 row affected (0.01 sec)

mysql>     insert into MyTable values(08,'W','27000');
Query OK, 1 row affected (0.01 sec)

mysql>     insert into MyTable values(09,'AS','28000');
Query OK, 1 row affected (0.00 sec)`

Create Table employee:

Now we create another table 'Employee' whose fieldnames and datatypes are specified.

mysql> CREATE TABLE Employee(
    ->              Empid int(10),
    ->              Empname varchar(60),
    ->              Salary int(90),
    ->              DOB date
    ->            );
Query OK, 0 rows affected (0.01 sec)

Insert Values into Employee:

mysql>     INSERT INTO Employee (Empid,Empname,Salary,DOB) VALUES
    ->     (01,'Habib',2014,20041202),
    ->     (02,'Karan',4021,20030411),
    ->     (03,'Samia', 22,20080223),
    ->     (04,'Hui Ling', 25,20081015),
    ->     (05,'Yumie', 29,19990126);
Query OK, 5 rows affected (0.00 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query for Exists

The output table shows only those empid that matches from table 'mytable' with employee. The unmatchable records id are not displayed.

mysql> select empid from mytable where  EXISTS (select empid from employee);
+-------+
| empid |
+-------+
| 1     |
| 2     |
| 3     |
| 4     |
| 5     |
| 6     |
| 7     |
| 9     |
+-------+
8 rows in set (0.00 sec)

Mysql Exists

Mysql Exists

Mysql Exists

Tutorials