Subquery in SQL

A subquery in SQL is a query that is embedded within another query. It is also referred to as an inner query or a nested query. The result of the subquery is used as input to the main query, which then produces the final result set.

Subqueries are used to perform complex data manipulations and data retrievals. They allow you to use the result of one query as the input for another query. Subqueries can be used in different parts of a SQL statement, such as in the WHERE clause, the FROM clause, or the SELECT clause.

Here’s an example of a subquery that retrieves the names of all employees who earn a salary higher than the average salary of their department:

SELECT emp_name
FROM employee
WHERE salary > (SELECT AVG(salary)
FROM employee
WHERE dept_id = employee.dept_id)

In this example, the subquery (SELECT AVG(salary) FROM employee WHERE dept_id = employee.dept_id) retrieves the average salary for the department of each employee in the main query. The result of the subquery is then compared to the salary of each employee, and the names of employees whose salary is higher than the average salary of their department are returned.

Subqueries can also be used in conjunction with other SQL statements, such as JOINs, to retrieve data from multiple tables. Subqueries can make SQL statements more efficient and easier to read and maintain. However, they can also slow down the performance of a query if not used properly, so it’s important to use them judiciously.

subquery in sql with join statement:

Here’s an example of a subquery used in conjunction with a JOIN statement in SQL:

Suppose we have two tables: orders and customers. The orders table contains information about each customer's orders, including the customer's customer_id. The customers table contains information about each customer, including their customer_id and their country.

Suppose we want to retrieve a list of all customers who have placed an order from a country other than their own. We can do this using a subquery and a JOIN statement, like this:

SELECT c.customer_id, c.customer_name
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country <> (
SELECT country
FROM customers
WHERE customer_id = o.customer_id
);

In this query, the subquery (SELECT country FROM customers WHERE customer_id = o.customer_id) retrieves the country of the customer who placed each order in the orders table. The subquery is then used to compare the customer's country to the country of the customer in the customers table. The JOIN statement ensures that the customer information is retrieved from the customers table and matched to the appropriate order in the orders table.

The final result set contains the customer_id and customer_name for all customers who have placed an order from a country other than their own.