Introduction to SQL Subqueries

Introduction

This tutorial aims to introduce you to the concept of SQL subqueries. A subquery, also known as a nested query or inner query, is a query inside another SQL query which allows you to perform complex operations within a single SQL statement.

By the end of this tutorial, you'll be able to:
- Understand the concept of SQL subqueries
- Write your own subqueries
- Use subqueries in SELECT, INSERT, UPDATE, DELETE statements, and in conjunction with various SQL operators.

Prerequisites: Basic understanding of SQL and databases, and familiarity with SQL SELECT statement.

Step-by-Step Guide

A subquery is a query that is embedded in the WHERE or HAVING clause of another SQL query. Subqueries can return individual values or a list of records; Note that Subqueries must be enclosed with parenthesis.

Basic Syntax

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

Best Practices and Tips

• Subqueries are executed first and then the result is passed to the main query.
• Subqueries can be nested to multiple levels.
• Subqueries can be used with SELECT, INSERT, UPDATE, DELETE statements along with expression operator.

Code Examples

Example 1: Using Subquery with SELECT statement

SELECT employee_name, salary
FROM employee
WHERE salary > (SELECT AVG(salary) FROM employee);

This SQL statement finds all employees whose salary is above the average salary.

Example 2: Using Subquery with DELETE statement

DELETE FROM orders
WHERE order_id NOT IN (SELECT order_id FROM shipped_orders);

This SQL statement deletes all orders that have not been shipped.

Summary

Key points covered:
- An SQL subquery is a powerful tool that allows us to perform complex operations.
- Subqueries are performed first and their results are used in the main query.
- Subqueries can be used with various SQL operators and within SELECT, INSERT, UPDATE, DELETE statements.

Next steps for learning:
- Try writing your own subqueries.
- Learn about correlated subqueries and EXISTS clause.

Practice Exercises

Try these exercises to practice what you've learnt.

1. Exercise 1: Write an SQL statement to find the names of all employees whose salary is above that of 'Jones'.

2. Hint: Use a subquery to find 'Jones'' salary.

3. Exercise 2: Write an SQL statement to delete all products in the 'Electronics' category that have less than 10 items in stock.

4. Hint: Use a subquery to find all 'Electronics' products.

Solutions

1. Solution to Exercise 1:

SELECT employee_name
FROM employee
WHERE salary > (SELECT salary FROM employee WHERE employee_name = 'Jones');

This statement first finds the salary of 'Jones', then finds all employees whose salary is greater than that.

1. Solution to Exercise 2:

DELETE FROM products
WHERE category = 'Electronics' AND product_id IN (SELECT product_id FROM inventory WHERE quantity < 10);

This statement first finds the IDs of all products that have less than 10 items in stock, then deletes those products from the 'Electronics' category.