Sql Select

SQL SELECT statement is used to query data from a single or multiple tables in a database. The Select statement like all other sql statements is not case sensitive. It can be used to select particular columns, set of rows, joining tables and grouping data and also performing calculations.

An example of SELECT statement to select particular columns from a table is shown below.


    SELECT column1, column2
    FROM mytable;

The list of columns that will be returned is listed as comma separated list. The table name needs to be specified after the FROM clause. The semi-colon (;) is not necessary but it is used to separate multiple sql queries.

All the columns in a table can be returned from a table by specifying asterisk (*) operator instead of the list of the columns.


    SELECT *
    FROM mytable;

As mentioned previously, sql statements are not case sensitive and therefore the following two queries return the same data set.


    SELECT *
    FROM mytable;


    select *
    from mytable;

For demonstration purpose, the following table is used to show examples of how to use the SELECT statement.

Example: Query all columns of a table

The following query can be used to display all the columns and rows of a table.


    SELECT *
    FROM mytable;

The above query produces the following output.

The select all query using the asterisk (*) operator is easy to use but it should be used wisely since it will return all the data. For example, only data that meets certain criteria is required (filtering using WHERE clause), or maybe only certain columns are required instead of all, or maybe only the first n number of rows should be returned as the user may not need all the data at once.

Example: Query certain columns of a table

The following query can be used to display all the columns and rows of a table.


    SELECT Name, State
    FROM mytable;

The above query produces the following output.

Example: Perform Calculation using Sql Query

The following query can be used to calculate the age of the people in the table based on the date of birth.


    SELECT Name, DateOfBirth, (DATEDIFF(year, DateOfBirth, CURRENT_TIMESTAMP)) AS Age
    FROM myTable;

The above query produces the following output.

The above query is run in Sql Server. The CURRENT_TIMESTAMP function returns the current date and time, the DATEDIFF function returns the difference between two dates and the first parameter defines what will be returned - year, month or day.

In the above query, the Age output is an alias for the (DATEDIFF(year, DateOfBirth, CURRENT_TIMESTAMP)) function. The AS clause is optional. Aliases can also be defined for any columns. For example, the following query applies aliases for all the columns.


    SELECT Name AS myName, DateOfBirth AS BornIn, (DATEDIFF(year, DateOfBirth, CURRENT_TIMESTAMP)) AS Age
    FROM myTable;