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 |
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.
No comments:
Post a Comment
NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.