Page 98 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

7 Performance Considerations : Be cautious when using subqueries, especially correlated subqueries, as
they can impact query performance. In some cases, alternative methods like JOINs or CTEs (Common Table
Expressions) might be more efficient.
8 Subquery Limitations : Some database systems have limitations on the complexity and nesting depth of
subqueries. Always consult the documentation for your specific database system to understand its capabilities
and limitations regarding subqueries.
Syntax
While there isn’t a single, universal syntax for subqueries in SQL because their usage can vary depending on the
specific query and database system, subqueries are commonly used in conjunction with the SELECT statement.
Here’s a general template for a subquery within a SELECT statement:
SELECT column1, column2, ...

FROM table1
WHERE columnN operator (SELECT columnX FROM tableY WHERE condition);
In this template:
• column1, column2, etc., represent the columns you want to retrieve in your main query.

• table1 is the main table you’re querying.
• columnN is a column from table1 that you want to compare using an operator.
• operator is a comparison operator like =, <, >, IN, etc.
• The subquery within parentheses (SELECT columnX FROM tableY WHERE condition) is embedded in the
WHERE clause and retrieves data from another table (tableY) based on a specific condition.
Subqueries with the INSERT Statement
Subqueries can also be used with the INSERT statement in SQL to insert data into a table based on the results of
a subquery. This allows you to populate a table with data derived from another table or the result of a subquery.
Here’s a basic syntax for using subqueries with the INSERT statement:
INSERT INTO target_table (column1, column2, ...)
SELECT expression1, expression2, ...

FROM source_table
WHERE condition;
In this syntax:
• target_table is the table into which you want to insert data.
• column1, column2, etc., are the columns in target_table where you want to insert data.

• expression1, expression2, etc., are expressions or values that you want to insert into the corresponding
columns in target_table.
• source_table is the table or subquery that provides the data you want to insert.

• condition is an optional condition that filters the data from the source_table before insertion.
Here’s an example to illustrate how you might use a subquery with the INSERT statement:
Let’s say you have two tables, employees and new_hires, and you want to insert data into the employees table
from the new_hires table based on a certain condition:

INSERT INTO employees (employee_id, employee_name, salary)
SELECT new_id, new_name, new_salary
FROM new_hires
WHERE new_salary > 50000;

85

CITS : IT&ITES - Computer software application - Lesson 18 - 36