MySQL - Subqueries Statement

---

---

MySQL Subqueries Statement

You can write a query with in a query in MySQL this is known as a subquery or, an inner query or, a Nested query. Usually, a sub query is embedded within the where clause.

A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

Following are the rules to be followed while writing subqueries −

• The subqueries must be enclosed within parentheses.
• An ORDER BY command cannot be used in a subquery, we can use the GROUP BY clause instead.
• If a subquery returns more than one row you can use only multiple value operators such as the IN.
• A subquery cannot be immediately enclosed in a set function.
• The BETWEEN operator cannot be used with a subquery. However, the BETWEEN operator can be used within the subquery.

Example

Assume we have created two tables and populated them using the following queries −

CREATE TABLE data1 (num INT);

Now, let's insert "num" column value as 1 into the data1 table −

INSERT INTO data1 VALUES (1);

This is another table named data2 −

CREATE TABLE data2 (num INT);

Let's insert a value into the data2 table −

INSERT INTO data2 VALUES (10252);

Following is an example of the nested query −

SELECT (SELECT num FROM data2) FROM data1;

Output

Following is the output of the above query −

(SELECT num FROM data2)
10252

You can use TABLE instead of select statement and create a nested query as follows −

SELECT (TABLE data2) FROM data1;

Output

The above query generates the following output −

(TABLE data2)
10252

MySQL Subquery with Comparison Operator

The most frequently used form of sub query is the subquery with the comparison operators. Following is the syntax of this −

non_subquery_operand comparison_operator (subquery)

Example

Assume we have created a table named EMP using the CREATE statement as shown below −

CREATE TABLE EMP (
   ID INT,
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT
);

Now, let us insert values in the above created table using the INSERT statement as shown below −

INSERT INTO EMP VALUES
(1, 'Krishna', 'Sharma', 19, 'M', 2000),
(2, 'Raj', 'Kandukuri', 20, 'M', 7000),
(3, 'Ramya', 'Ramapriya', 25, 'F', 5000),
(4, 'Alexandra', 'Botez', 26, 'F', 3000);

Following query arranges and retrieves the contents of the EMP table based on the FIRST_NAME column −

SELECT * FROM EMP WHERE ID IN (SELECT ID FROM EMP WHERE INCOME > 4000);

Output

The above mysql query will generate the output displayed below −

ID	FIRST_NAME	LAST_NAME	AGE	SEX	INCOME
2	Raj	Kandukuri	20	M	7000
3	Ramya	Ramapriya	25	F	5000

MySQL Subquery with IN or NOT-IN Operator

Using the IN or NOT-IN clauses we can include or exclude the records from another table.

Example

Assume we have created another table named EMP2 using the CREATE statement as shown below −

CREATE TABLE EMP2 (
   ID INT,
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   INCOME INT
);

Now, let's insert four records into the EMP2 table −

INSERT INTO EMP2 VALUES
(1, 'Krishna', 'Sharma', 19, 2000),
(2, 'Raj', 'Kandukuri', 20, 7000),
(3, 'Rahman', 'Ali', 25, 6000),
(4, 'Sudha', 'Sastry', 29, 9000);

Following query demonstrates the usage of this query −

SELECT * FROM EMP2 WHERE FIRST_NAME NOT IN (SELECT FIRST_NAME FROM EMP);

Output

Following is the output of the above query −

ID	FIRST_NAME	LAST_NAME	AGE	INCOME
3	Rahman	Ali	25	6000
4	Sudha	Sastry	29	9000

Subqueries with ANY, ALL, or SOME

You can compare the values returned by the subquery using ALL, ANY or SOME clauses. You need to use these clauses before the subquery.

Example

Assume we have created a table named data1 using the CREATE statement as shown below −

CREATE TABLE data1 (
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   INCOME INT
);

Now, let's insert some records into the data1 table −

INSERT INTO data1 VALUES
('Rahman', 'Ali', 25, 6000),
('Sudha', 'Sastry', 29, 9000);

Assume we have created another table named data2 shown below −

CREATE TABLE data2 (
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   INCOME INT
);

Now, let us insert two records into the data2 table −

INSERT INTO data2 VALUES
('Krishna', 'Sharma', 9000),
('Raj', 'Kandukuri', 7000);

Following query demonstrates the usage of this query −

SELECT * FROM data1 WHERE INCOME > ANY (SELECT INCOME FROM data2);

Output

The above mysql query generates the following output −

FIRST_NAME	LAST_NAME	AGE	INCOME
Sudha	Sastry	29	9000

Row Subqueries

If you need to get a single row you should use the row subquery.

Example

Suppose we have created a table with name EMPLOYEE and populated data into it as shown below −

CREATE TABLE EMPLOYEE(
   ID INT NOT NULL,
   FIRST_NAME CHAR(20) NOT NULL,
   LAST_NAME CHAR(20),
   AGE INT,
   SEX CHAR(1),
   INCOME FLOAT,
   CONTACT INT
);

Now, let's insert some records into the Employee table −

INSERT INTO Employee VALUES
(101, 'Ramya', 'Rama Priya', 27, 'F', 9000, 101),
(102, 'Vinay', 'Bhattacharya', 20, 'M', 6000, 102);

And, if we have created another table and populated it as −

CREATE TABLE CONTACT(
   ID INT NOT NULL,
   FIRST_NAME CHAR(20) NOT NULL,
   EMAIL CHAR(20) NOT NULL,
   PHONE LONG,
   CITY CHAR(20)
);

Now, lets try to insert some records into the CONTACT table using the INSERT statement shown below −

INSERT INTO CONTACT VALUES 
(101, 'Ramya', 'ramya@mymail.com', 984801234, 'Hyderabad'), 
(102, 'Vinay', 'vinay@mymail.com', 984804321, 'Vishakhapatnam');

Following query demonstrates the usage of this query −

SELECT * FROM CONTACT WHERE ROW(ID, FIRST_NAME) = 
(SELECT ID, FIRST_NAME FROM EMPLOYEE WHERE id = 10);

Subqueries with EXISTS or NOT EXISTS

The EXISTS operator is a Boolean operator that returns either true or false result. It is used with a subquery and checks the existence of data in a subquery. If a subquery returns any record at all, this operator returns true. Otherwise, it will return false.

The NOT EXISTS operator used for negation that gives true value when the subquery does not return any row. Otherwise, it returns false. Both EXISTS and NOT EXISTS used with correlated subqueries. The following example illustrates it more clearly. Suppose we have a table customer and order that contains the data as follows −

CREATE TABLE CUSTOMER (
   cust_id INT, 
   name VARCHAR(20), 
   occupation VARCHAR(20), 
   age INT
);

Now, let us insert three records into the CUSTOMER table −

INSERT INTO CUSTOMER VALUES 
(101, 'Peter', 'Engineer', 32),
(102, 'Joseph', 'Developer', 30),
(103, 'Jhon', 'HR', 23);

Assume, we have created another table named ORDERS using the CREATE statement shown below −

CREATE TABLE ORDERS(
   order_id INT, 
   cust_id INT, 
   prod_name VARCHAR(20), 
   order_date VARCHAR(20)
);

Let's try to insert some records into it −

INSERT INTO ORDERS VALUES 
(1, 101, 'Laptop', 32),
(2, 102, 'Desktop', 30),
(3, 103, 'TV', 23);

Following query demonstrates the usage of this query −

SELECT name, occupation, age FROM customer C
WHERE EXISTS (SELECT * FROM Orders O
WHERE C.cust_id = O.cust_id);

Output

The above query will produce the following output −

name	occupation	age
Peter	Engineer	32
Joseph	Developer	30
Jhon	HR	23