- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
Which one is preferred in between MySQL EXISTS and IN while using in Subqueries?
The EXISTS tells if the query returned results or not while IN can be used for comparing one value with other. IN uses literal values.
Note − IN is preferred in a subquery whenever a subquery result is very small. If the subquery result is very large then EXISTS is used.
Since IN is a preferred choice for subquery results that are smaller, we are considering an example of IN.
To create a table.
mysql> create table InSubQueryDemo -> ( -> PNumber int, -> PName varchar(100) -> ); Query OK, 0 rows affected (0.60 sec)
Inserting records into the table.
mysql> insert into InSubQueryDemo values(1,'Laptop'); Query OK, 1 row affected (0.17 sec) mysql> insert into InSubQueryDemo values(2,'WirelessKeyboard'); Query OK, 1 row affected (0.21 sec) mysql> insert into InSubQueryDemo values(3,'WirelessMouse'); Query OK, 1 row affected (0.12 sec)
Displaying all records.
mysql> select *from InSubQueryDemo;
The following is the output.
+---------+------------------+ | PNumber | PName | +---------+------------------+ | 1 | Laptop | | 2 | WirelessKeyboard | | 3 | WirelessMouse | +---------+------------------+ 3 rows in set (0.00 sec)
The following is the syntax for IN, that includes a subquery to fetch “PNumber” greater than 1.
mysql> select *from InSubQueryDemo -> where PNumber IN (select PNumber from InSubQueryDemo WHERE PNumber > 1);
Here is the output.
+---------+------------------+ | PNumber | PName | +---------+------------------+ | 2 | WirelessKeyboard | | 3 | WirelessMouse | +---------+------------------+ 2 rows in set (0.04 sec)
- Related Articles
- Which one is preferred between a large table or multiple small tables in MySQL?
- Difference between CORRELATED and UNCORRELATED subqueries in DB2
- Difference between correlated and non-collreated subqueries in SQL
- Which rows are returned while using LIMIT with OFFSET in MySQL?
- Check if a table is empty or not in MySQL using EXISTS
- Why big tag is not in HTML5 while small tag exists?
- Which one is better in between SQLite and Shared Preferences?
- What are MySQL subqueries and its general categories?
- What is the difference Between AND, OR operator in MySQL while Retrieving the Rows?
- Check if table exists in MySQL and display the warning if it exists?
- Which one is better POW() or POWER() in MySQL?
- Which one is the fastest between children() and find() in jQuery?
- Drop trigger if exists in MySQL?
- What is the use of EXIST and EXIST NOT operator with MySQL subqueries?
- Which one is better in MySQL - NULL or empty string?