Sqlwhere Condition Statement usage

Here, we briefly introduce that the where condition statements can be expressed using join statements. Other problems can only be raised through subqueries. In Transact-SQL, statements that contain subqueries and statements that are semantically equivalent to statements that do not contain subqueries have no performance difference.

Here, we briefly introduce that the where condition statements can be expressed using join statements. Other problems can only be raised through subqueries. In Transact-SQL, statements that contain subqueries and statements that are semantically equivalent to statements that do not contain subqueries have no performance difference.

Queries in SELECT, INSERT, UPDATE, or DELETE statements or other subqueries. Subqueries can be used wherever expressions are allowed. In this example, the subquery is used as a column expression named MaxUnitPrice In the SELECT statement.

The Code is as follows:
Other USE AdventureWorks2008R2; GO SELECT Ord. SalesOrderID, Ord. OrderDate, (Select max (OrdDet. UnitPrice) FROM AdventureWorks. Sales. SalesOrderDetail AS OrdDet WHERE Ord. SalesOrderID = OrdDet. SalesOrderID) AS MaxUnitPrice FROM AdventureWorks2008R2. Sales. SalesOrderHeader AS Ord

A subquery is also called an internal query or an internal choice, and a statement containing a subquery is also called an external query or an external choice.

Many Transact-SQL statements that contain subqueries can be expressed using join statements. Other problems can only be raised through subqueries. In Transact-SQL, statements that contain subqueries and statements that are semantically equivalent to statements that do not contain subqueries have no performance difference. However, in some cases where you must check the existence, using a join produces better performance. Otherwise, to ensure that duplicate values are eliminated, nested queries must be processed for each result of an external query. In these cases, the connection method will produce better results. The following example shows the SELECT subquery and SELECT join that return the same result set:

The Code is as follows:

Other /* SELECT statement built using a subquery .*/ SELECT Name FROM AdventureWorks2008R2. Production. Product WHERE ListPrice = (SELECT ListPrice FROM AdventureWorks2008R2. Production. Product WHERE Name = 'chainring bolts '); /* SELECT statement built using a join that returns The same result set .*/ SELECT Prd1. Name FROM AdventureWorks2008R2. Production. Product AS Prd1 JOIN AdventureWorks2008R2. Production. Product AS Prd2 ON (Prd1.ListPrice = Prd2.ListPrice) WHERE Prd2. Name = 'chainring bolts ';

Subqueries nested in external SELECT statements include the following components:

A general SELECT query that contains the general selection list component.

A regular FROM clause that contains one or more table or view names.

Optional WHERE clause.

Optional group by clause.

Optional HAVING clause.

The SELECT query of A subquery is always enclosed in parentheses. It cannot contain the COMPUTE or for browse clause. If the TOP clause is specified at the same time, it can only contain the order by clause.

Subqueries can be nested in the WHERE or HAVING clause of external SELECT, INSERT, UPDATE, or DELETE statements, or in other subqueries. Although the nesting restriction varies depending on the complexity of other expressions in the available memory and query, it is possible to nest to 32 layers. Individual queries may not support 32-layer nesting. You can use subqueries wherever expressions are available, as long as they return a single value.

If a table appears only in a subquery but not in an external query, the columns in the table cannot be included in the output (selected list of external queries.

Statements that contain subqueries generally use one of the following formats:

The Code is as follows:
WHERE expression [NOT] IN (subquery) WHERE expression comparison_operator [ANY | ALL] (subquery) WHERE [NOT] EXISTS (subquery)