SELECT Statement

Basic Syntax

The simplest form of the SELECT statement retrieves all columns and all rows from a table.

SELECT *
FROM TableName;
                

To retrieve specific columns, list them after the SELECT keyword, separated by commas.

SELECT Column1, Column2, ...
FROM TableName;
                

WHERE Clause

The WHERE clause is used to filter records. It extracts only those records that fulfill a specified condition.

SELECT Column1, Column2
FROM TableName
WHERE Condition;
                

Conditions can involve comparison operators like =, >, <, >=, <=, <> (or !=), and logical operators like AND, OR, NOT.

Example:

SELECT ProductName, Price
FROM Products
WHERE Price > 50.00 AND Category = 'Electronics';
                

ORDER BY Clause

The ORDER BY clause is used to sort the result-set in ascending or descending order. By default, it sorts in ascending order.

SELECT Column1, Column2
FROM TableName
ORDER BY ColumnName [ASC|DESC];
                

Example:

SELECT CustomerName, City
FROM Customers
ORDER BY City DESC;
                

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 such as COUNT(), MAX(), MIN(), SUM(), and AVG().

SELECT ColumnName, AGGREGATE_FUNCTION(ColumnName)
FROM TableName
WHERE Condition
GROUP BY ColumnName
ORDER BY ColumnName;
                

Example:

SELECT Country, COUNT(CustomerID) AS NumberOfCustomers
FROM Customers
GROUP BY Country
ORDER BY NumberOfCustomers DESC;
                

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 groups created by the GROUP BY clause.

SELECT ColumnName, COUNT(ColumnName)
FROM TableName
GROUP BY ColumnName
HAVING COUNT(ColumnName) > 5;
                

Example:

SELECT City, COUNT(CustomerID) AS CustomerCount
FROM Customers
GROUP BY City
HAVING COUNT(CustomerID) > 10
ORDER BY CustomerCount DESC;
                

JOIN Operations

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

INNER JOIN

Returns records that have matching values in both tables.

SELECT ColumnNames
FROM Table1
INNER JOIN Table2
ON Table1.ForeignKeyColumn = Table2.PrimaryKeyColumn;
                

LEFT JOIN (or LEFT OUTER JOIN)

Returns all records from the left table, and the matched records from the right table. If there is no match, the result is NULL on the right side.

SELECT ColumnNames
FROM Table1
LEFT JOIN Table2
ON Table1.ForeignKeyColumn = Table2.PrimaryKeyColumn;
                

RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all records from the right table, and the matched records from the left table. If there is no match, the result is NULL on the left side.

SELECT ColumnNames
FROM Table1
RIGHT JOIN Table2
ON Table1.ForeignKeyColumn = Table2.PrimaryKeyColumn;
                

FULL OUTER JOIN

Returns all records when there is a match in either left or right table. Returns NULL for unmatched rows.

SELECT ColumnNames
FROM Table1
FULL OUTER JOIN Table2
ON Table1.ForeignKeyColumn = Table2.PrimaryKeyColumn;
                

Aliases

Aliases can be used to give a temporary name to a table or a column. This is useful for making queries shorter and more readable, especially when dealing with complex joins.

Table Alias:

SELECT c.CustomerName, o.OrderID
FROM Customers AS c
INNER JOIN Orders AS o
ON c.CustomerID = o.CustomerID;
                

Column Alias:

SELECT ProductName AS Name, Price AS UnitPrice
FROM Products;
                

Subqueries

A subquery is a query within a query. It can be used in the WHERE clause, FROM clause, or SELECT clause.

Example in WHERE clause:

SELECT ProductName
FROM Products
WHERE CategoryID IN (SELECT CategoryID FROM Categories WHERE CategoryName = 'Beverages');