SQL SELECT Statement

SQL SELECT Statement

Select statements are used to select data from a table.

The result is stored in a result table, called a result set.

SQL SELECT Syntax

SELECT column name from table name

And:

SELECT * FROM table name

Note: The SQL statement is not case sensitive. Select is equivalent to select.

SQL SELECT Instance

To get the contents of a column named "LastName" and "FirstName" (from a database table named "Persons"), use a SELECT statement like this:

SELECT Lastname,firstname from Persons

"Persons" table: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

Results:

LastName	FirstName
Adams	John
Bush	George
Carter	Thomas

SQL SELECT * Instance

Now we want to pick all the columns from the "Persons" table.

Use the symbol * instead of the name of the column, just like this:

*From Persons

Tip: An asterisk (*) is a shortcut to select all columns.

Results: City

Id	LastName	FirstName	Address
1	Adams	John	Oxford Street	London
2	Bush	George	Fifth Avenue	New York
3	Carter	Thomas	Changan Street	Beijing

SQL SELECT DISTINCT Statement

In the table, duplicate values may be included. That's not a problem, but sometimes you might want to just list different values (distinct).

Keyword DISTINCT is used to return only different values.

Grammar:

SELECT DISTINCT column name from table name

Using DISTINCT Keywords

If you want to pick all the values from the company column, we need to use the SELECT statement:

SELECT Company from Orders

"Orders" table: Company

	OrderNumber
Ibm	3532
W3school	2356
Apple	4698
W3school	6953

Results:

Company
Ibm
W3school
Apple
W3school

Note that in the result set, W3school is listed two times.

To select only a different value from the company column, we need to use the Select DISTINCT statement:

DISTINCT

Results:

Company
Ibm
W3school
Apple

The WHERE clause is used to specify the criteria for selection.

WHERE clause

To conditionally select data from a table, you can add a WHERE clause to the SELECT statement.

Grammar

SELECT column name from table name WHERE column operator value

The following operators can be used in the WHERE clause:

operator	Description
=	Equals
<>	Not equal to
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
Between	Within a range
Like	Search for a pattern

Note: In some versions of SQL, the operator <> can be written as! =.

Using the WHERE clause

If you want to select only people who live in the city "Beijing", we need to add a WHERE clause to the SELECT statement:

WHERE City=‘Beijing‘

"Persons" table City Year

LastName	FirstName	Address
Adams	John	Oxford Street	London	1970
Bush	George	Fifth Avenue	New York	1975
Carter	Thomas	Changan Street	Beijing	1980
Gates	Bill	Xuanwumen 10	Beijing	1985

Results: City Year

LastName	FirstName	Address
Carter	Thomas	Changan Street	Beijing	1980
Gates	Bill	Xuanwumen 10	Beijing	1985

Use of quotation marks

Note that we use single quotes around the condition values in the example.

SQL uses single quotation marks to wrap text values (most database systems also accept double quotes). If it is a numeric value , do not use quotation marks.

Text value:

FirstName=‘Bush‘FirstName=Bush

Numerical:

Year>1965Year>‘1965‘

SQL SELECT Statement