Blog Archive

SQL Select statement Syntax cheat sheet and examples

Select SQL statement Syntax cheat sheet is very helpful. You can use these for any embedded SQL programming language such as SQLRPGLE on iSeries(AS400), for SQL in java programs using JDBC connections. You can also query files on iSeries using STRSQL command which provides a SQL command line.

SQL Select Statement Syntax

SELECT [ALL | DISTINCT] columnname1 [,columnname2]
FROM tablename1 [,tablename2]
[WHERE condition] [ and|or condition...]
[GROUP BY column-list]
[HAVING "conditions]
[ORDER BY "column-list" [ASC | DESC] ]

The output is either displayed or stored in a the result-set.

Select the complete table
SELECT * FROM table_name

Select specific columns
SELECT column_name(s) FROM table_name

SQL Distinct Syntax

The Distinct keyword is used to return only unique values within the selection criteria.
SELECT DISTINCT column_name(s) FROM table_name

SQL Where Syntax

The Where clause is used to extract only those records that fulfill a specified criterion.
SELECT column_name(s) FROM table_name WHERE column_name operator value

With the WHERE clause, the following operators can be used:
Operator Description
= Equal
<> Not equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN If you know the exact value you want to return for at least one of the columns
Please Note: In some versions of SQL the <> operator may be written as !=

SQL Order by Syntax

The Order by keyword is used to sort the result-set by a specified column(s).
Default sorting for records is ascending order, If you want to sort the records in a descending order, you must use the DESC keyword.
SELECT column_name(s) FROM table_name ORDER BY column_name(s) ASC|DESC

SQL Group By and Order By Syntax

GROUP BY is an optional clause in a query. It follows the WHERE clause or the FROM clause if the WHERE clause is missing. A query containing a GROUP BY clause is a Grouping Query. The GROUP BY clause has the following general format:

GROUP BY column-1 [, column-2] ...

column-1 and column-2 are the grouping columns. They must be names of columns from tables in the FROM clause; they can't be expressions.
SELECT b.customerid, a.employeeid , COUNT(a.orderid) AS cnt
FROM orders a
INNER JOIN customers b ON a.customerid = b.customerid
GROUP BY b.customerid ,a.employeeid
HAVING COUNT(a.orderid) > 5
ORDER BY b.customerid, cnt DESC

SELECT company, customer , COUNT(orderId) AS cnt 
FROM orders_table WHERE order_date > 20100101 
GROUP BY company, customer
HAVING count(orderId) > 11 
ORDER BY company, customer 

Like the WHERE Clause, HAVING filters the query result rows. WHERE filters the rows from the FROM clause. HAVING filters the grouped rows (from the GROUP BY clause) or the aggregate row (for Aggregate Queries).

SQL Top Clause Syntax

The TOP clause is used to specify the number of records to return. But this varies from database to database.
SELECT TOP number|percent column_name(s)FROM table_name

Here are a few exceptions...

MySQL Syntax

SELECT column_name(s) FROM table_name LIMIT number

Oracle Syntax

SELECT column_name(s) FROM table_name WHERE ROWNUM <= number 


DB2 Syntax

SELECT column_name(s) FROM table_name FETCH FIRST number rows only

SQL LIKE Operator Syntax

The LIKE operator is used to search for a specified pattern in a column.
SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern

SQL wildcards can substitute for one or more characters when searching for data in a database. With SQL, the following wildcards can be used:
Wildcard Description
% A substitute for zero or more characters
_ A substitute for exactly one character
[charlist] Any single character in charlist
[^charlist]or[!charlist] Any single character not in charlist

SQL IN Syntax

The IN operator allows you to specify multiple values in a WHERE clause.
SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...)

SQL BETWEEN Syntax

The BETWEEN operator selects a range of data between two values.
SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2

SQL Alias Syntax

You can give a table or a column another name by using an alias. This is useful if you have very long or complex table names or column names.
SQL Alias Syntax for Tables
SELECT column_name(s) FROM table_name AS alias_name

SQL Alias Syntax for Columns
SELECT column_name AS alias_name FROM table_name

SQL INNER JOIN Syntax

The INNER JOIN keyword return rows when there is at least one match in both tables.
SELECT column_name(s) FROM table_name1 INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL LEFT JOIN Syntax

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matches in the right table (table_name2).
SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL RIGHT JOIN Syntax

The RIGHT JOIN keyword returns all the rows from the right table (table_name2), even if there are no matches in the left table (table_name1).
SELECT column_name(s) FROM table_name1 RIGHT JOIN table_name2
ON table_name1.column_name=table_name2.column_name

SQL UNION Syntax

The UNION operator is used to combine the result-set of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns and the columns data types must match. Also, the columns in each SELECT statement must be in the same order.
SELECT column_name(s) FROM table_name1
UNION
SELECT column_name(s) FROM table_name2

Please Note: The UNION operator selects only distinct values by default. To allow duplicate values, use UNION ALL.

Recommended Reading