SQL case, subquery

CASE

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

SUBQUERY

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, NOT IN

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

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

FROM

SELECT *
FROM (
	SELECT CustomerId
	FROM customers
	WHERE CustomerId < 10
	)