SQL – Sub Queries

1. Subqueries must be enclosed within parentheses.
2. A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
3. An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY.

Certainly! Here’s the syntax for using a subquery in SQL Server:

<span class="hljs-keyword">SELECT</span> column1, column2, ...
<span class="hljs-keyword">FROM</span> table1
<span class="hljs-keyword">WHERE</span> column1 <span class="hljs-operator">=</span> (<span class="hljs-keyword">SELECT</span> column1 <span class="hljs-keyword">FROM</span> table2 <span class="hljs-keyword">WHERE</span> <span class="hljs-keyword">condition</span>);


In the above syntax, the subquery is enclosed within parentheses and is used as a value in the main query’s WHERE clause. The subquery retrieves a single value, such as a column value or an aggregate result, which is then compared with the column in the main query’s WHERE clause.

Here’s an example that demonstrates the usage of a subquery:

<span class="hljs-keyword">SELECT</span> employee_name, department
<span class="hljs-keyword">FROM</span> employees
<span class="hljs-keyword">WHERE</span> department <span class="hljs-operator">=</span> (<span class="hljs-keyword">SELECT</span> department <span class="hljs-keyword">FROM</span> managers <span class="hljs-keyword">WHERE</span> manager_name <span class="hljs-operator">=</span> <span class="hljs-string">'John'</span>);


In the above example, the subquery retrieves the department associated with the manager named ‘John’, and the main query retrieves the employee names and departments of employees who belong to that department.

Please note that this is just a basic example, and subqueries can be used in various ways, such as in the SELECT clause, JOIN conditions, and more, depending on the requirements of your specific SQL query.