T-SQL Clauses

This section provides detailed information about the clauses used in Transact-SQL (T-SQL) statements. Clauses are keywords that specify actions to be performed or conditions that must be met. Understanding these clauses is fundamental to writing effective and efficient T-SQL queries and statements.

Common T-SQL Clauses

SELECT Clause

The SELECT clause specifies the columns to be retrieved from a table or view.

SELECT column1, column2, ...
FROM table_name;

You can also use wildcards like * to select all columns.

SELECT *
FROM table_name;

FROM Clause

The FROM clause specifies the table or tables from which to retrieve data. It is used in SELECT, UPDATE, and DELETE statements.

SELECT column1
FROM table_name;

WHERE Clause

The WHERE clause filters records based on a specified condition. Only records that satisfy the condition are returned.

SELECT column1, column2
FROM table_name
WHERE column1 > 100;

GROUP BY Clause

The GROUP BY clause groups rows that have the same values in specified columns into summary rows, like "find the number of customers in each city". It is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG).

SELECT COUNT(customer_id), city
FROM customers
GROUP BY city;

HAVING Clause

The HAVING clause is used to filter groups based on a specified condition. It is similar to the WHERE clause, but it operates on grouped data.

SELECT COUNT(customer_id), city
FROM customers
GROUP BY city
HAVING COUNT(customer_id) > 10;

ORDER BY Clause

The ORDER BY clause sorts the result set in ascending (ASC) or descending (DESC) order based on one or more columns.

SELECT product_name, price
FROM products
ORDER BY price DESC;

JOIN Clauses

JOIN clauses are used to combine rows from two or more tables based on a related column between them.

INNER JOIN: Returns rows when there is a match in both tables.
LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table.
RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table.
FULL JOIN (or FULL OUTER JOIN): Returns all rows from both tables.
CROSS JOIN: Returns the Cartesian product of the rows from the tables.

SELECT orders.order_id, customers.customer_name
FROM orders
INNER JOIN customers
ON orders.customer_id = customers.customer_id;

INSERT INTO Clause

The INSERT INTO clause is used to add new rows to a table.

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

UPDATE Clause

The UPDATE clause is used to modify existing records in a table.

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

DELETE Clause

The DELETE clause is used to delete existing records from a table.

DELETE FROM table_name
WHERE condition;

Other Important Clauses

TOP/LIMIT: Restricts the number of rows returned.
DISTINCT: Removes duplicate rows from the result set.
OFFSET FETCH: Used with ORDER BY for pagination.
FOR XML: Formats query results as XML.
FOR JSON: Formats query results as JSON.

Explore the links in the left navigation pane for more in-depth information on each clause, including syntax, examples, and usage scenarios.