SELECT and SELECT DISTINCT SQL QUERY

SELECT STATEMENT

SELECT identifies what columns to fetch and FROM identifies which table to refer. The SQL SELECT statement returns a result set of records from one or more tables. A SELECT statement retrieves zero or more rows from one or more database tables or database views. In most applications, SELECT is the most commonly used data query language (DQL) command. As SQL is a declarative programming language, SELECT queries specify a result set, but do not specify how to calculate it. The database translates the query into a "query plan" which may vary between executions, database versions and database software. This functionality is called the "query optimizer" as it is responsible for finding the best possible execution plan for the query, within applicable constraints.

The SELECT statement has many optional clauses:

WHERE specifies which rows to retrieve.

GROUP BY groups rows sharing a property so that an aggregate function can be applied to each group.

HAVING selects among the groups defined by the GROUP BY clause.

ORDER BY specifies an order in which to return the rows.

AS keyword provides an alias which can be used to temporarily rename tables or columns.

Syntax

SELECT *| {[DISTINCT] COLUMN_NAME|EXPRESSION [ALIAS],...}

FROM TABLE_NAME;

SELECT	Is a list of one or more columns
*	Selects all columns
DISTINCT	Suppresses duplicates
column|expression	Selects the named column or the expression
alias	Gives selected columns different headings
FROM table	specifies the table to refer containing the columns

Example

Below SELECT example will show all the rows from students table.

SELECT * FROM STUDENTS;

DISTINCT STATEMENT

A column may contain several duplicate values; and generally you merely wish to list the various (distinct) values. The SELECT DISTINCT clause is used with SELECT statement to eliminate all the duplicate records and show only distinctive records. In SQL, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value. DISTINCT can be used with aggregates function and operates only on a single column.

Syntax

SELECT DISTINCT COLUMN_NAME1, COLUMN_NAME2, ...
FROM TABLE_NAME;

Example

DISTINCT clause is used to find the unique values within one column in a table.

Below example shows 6 city from students table. There is a duplicate entry of central city.

SELECT CITY FROM STUDENTS;

By using DISTINCT clause we can eliminate this duplicity and fetch only unique city names.

SELECT DISTINCT CITY FROM STUDENTS;