Home Sql Mysql-example Mysql Exists

Related Tutorials


 
 

Share on Google+Share on Google+

Mysql Exists

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

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)
Advertisements

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: January 24, 2009

Related Tutorials

Discuss: Mysql Exists  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:1
Prudhvi
September 21, 2011
Correction needed

I sense something is wrong with the above example. Can any expert please check it once again and make correction(if any). If not try explaining why this result?
DMCA.com