SQL – Sub Queries

SQL Subqueries – Vista Academy

SQL Subqueries

Table of Contents

An SQL Subquery, is a SELECT query within another query. It is also known as Inner query or Nested query and the query containing it is the outer query.

The outer query can contain the SELECT, INSERT, UPDATE, and DELETE statements. We can use the subquery as a column expression, as a condition in SQL clauses, and with operators like =, >, <, >=, <=, IN, BETWEEN, etc.

Rules to be followed

Following are the rules to be followed while writing subqueries −

Subqueries must be enclosed within parentheses.
Subqueries can be nested within another subquery.
A subquery must contain the SELECT query and the FROM clause always.
A subquery consists of all the clauses an ordinary SELECT clause can contain: GROUP BY, WHERE, HAVING, DISTINCT, TOP/LIMIT, etc. However, an ORDER BY clause is only used when a TOP clause is specified. It can’t include COMPUTE or FOR BROWSE clause.
A subquery can return a single value, a single row, a single column, or a whole table. They are called scalar subqueries.

Subqueries with the SELECT Statement

Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows −

SELECT column_name [, column_name ] 
FROM table1 [, table2 ] 
WHERE  column_name 
OPERATOR (SELECT column_name [,column_name ] FROM table1 [, table2 ] [WHERE]);
  

Example
In the following query, we are creating a table named CUSTOMERS −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
  

Here, we are inserting records into the above-created table using INSERT INTO statement −

INSERT INTO CUSTOMERS VALUES 
(1, ‘Ramesh’, 32, ‘Ahmedabad’, 2000.00),
(2, ‘Khilan’, 25, ‘Delhi’, 1500.00),
(3, ‘Kaushik’, 23, ‘Kota’, 2000.00),
(4, ‘Chaitali’, 25, ‘Mumbai’, 6500.00),
(5, ‘Hardik’, 27, ‘Bhopal’, 8500.00),
(6, ‘Komal’, 22, ‘Hyderabad’, 4500.00),
(7, ‘Muffy’, 24, ‘Indore’, 10000.00);
  

The table is displayed as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Now, let us check the following subquery with a SELECT statement.

SELECT * FROM CUSTOMERS 
WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500);
  

This would produce the following result −

ID	NAME	AGE	ADDRESS	SALARY
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
7	Muffy	24	Indore	10000.00

Subqueries with the INSERT Statement

We can also use the subqueries along with the INSERT statements. The data returned by the subquery is inserted into another table.

The basic syntax is as follows −

INSERT INTO table_name [ (column1 [, column2 ]) ] 
   SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] 
   [ WHERE VALUE OPERATOR ]
  

Example
In the following example, we are creating another table CUSTOMERS_BKP with similar structure as CUSTOMERS table −

CREATE TABLE CUSTOMERS_BKP (
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),
   PRIMARY KEY (ID)
);
  

Now to copy the complete records of CUSTOMERS table into the CUSTOMERS_BKP table, we can use the following query −

INSERT INTO CUSTOMERS_BKP 
SELECT * FROM CUSTOMERS 
WHERE ID IN (SELECT ID FROM CUSTOMERS);
  

The above query produces the following output −

Query OK, 7 rows affected (0.01 sec)
Records: 7  Duplicates: 0  Warnings: 0
  

Verification

Using the SELECT statement, we can verify whether the records from CUSTOMERS table have been inserted into CUSTOMERS_BKP table or not −

SELECT * FROM CUSTOMERS_BKP;
  

The table will be displayed as −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Master Fundamental of SQL Written Edition English

Curriculum