SQL can also use if statements.
With CASE, we can receive different results depending on specific conditions.
SELECT CASE WHEN CustomerId <= 25 THEN 'GROUP 1' WHEN CustomerId <= 50 THEN 'GROUP 2' ELSE 'GROUP 3' END FROM customers
When you create a query statement, you can include other query statements.
The other query statement included is SUBQUERY.
Subqueries must be enclosed in brackets.
The result of a subquery is an individual value or a list of records.
You can use the results of a subquery as a single column.
SELECT CustomerId, CustomerId = (SELECT CustomerId FROM customers WHERE CustomerId = 2) FROM customers WHERE CustomerId < 6
IN can determine if a particular value exists in the subquery.
SELECT * FROM customers WHERE CustomerId IN (SELECT CustomerId FROM customers WHERE CustomerId < 10)
If NOT IN is used instead of IN, look up records that are less than 10 that are looked up in the subquery.
EXISTS or NOT EXISTS checks the records that exist in the returned subquery.
Returns TRUE if the record you want to query exists, or FALSE if not.
SELECT EmployeeId FROM employees WHERE EXISTS ( SELECT 1 FROM customers c WHERE c.SupportRepId = e.EmployeeId ) ORDER BY EmployeeId
SELECT * FROM ( SELECT CustomerId FROM customers WHERE CustomerId < 10 )