SQL - SELECT Queries

The SELECT queries retrieve rows/columns data from one or more existing tables.

Syntax:

SELECT column1, column2,...columnN 
FROM table_name

For the demo purpose, the following Employee table will be used in all the examples here.

Select All Columns

The * operator represents all the columns of a table. So, you don't need to specify each column name in the SELECT query to get data from all the columns.

SELECT * FROM Employee;

The above query returns all the rows and columns data from the Employee table, as shown below.

Select Specific Columns Data

Specify the column names in the SELECT statement to get the data only from those columns, as shown below.

SELECT EmpId, FirstName, LastName FROM Employee;

Alias for Columns and Table

You can specify an alias for one or more columns in the SELECT query. An alias is a temporary name to a table or column in the query.

Advantage of Alias:

• Alias makes a column more readable in the result set.
• Alias is used to give a small, abbreviated, and meaningful name to tables in the query so that it will be easy to refer tables in joining multiple tables.
• Alias helps us to identify which column belongs to which table in case of getting data from multiple tables.

The following query specifies the "Employee Id" for EmpId, and Name as an alias for the FirstName column in the SELECT query. Specify an alias in the single or double quotes if you want a space or other string in it.

SELECT EmpId "Employee Id", FirstName AS Name FROM Employee;

Operators in SELECT Statement

You can specify the operators in the select statement to perform some action on the column value. For example, the + operator in MS SQL Server and || operator in PostgreSQL, MySQL, and Oracle database concatenates string values or adds numeric values. The following concatenates two varchar columns in the result.

SELECT EmpId, FirstName + ' ' + LastName AS "Full Name" 
FROM Employee;

FROM Clause

The SELECT statement must have the FROM clause. The FROM clause is used to list down table names from which we want to select data and specify joins between those tables.

You can specify multiple tables in the FROM clause to select data from. However, if tables have the same columns, then you need to specify full column names like table_name.column_name in the SELECT query.

The following selects columns from two tables.

SELECT * FROM Employee, Deparatment;

SELECT Employee.*, Department.* FROM Employee, Deparatment;

SELECT emp.*, dept.* FROM Employee emp, Deparatment dept; 

SELECT emp.FirstName, dept.DipartmentName FROM Employee emp, Deparatment dept;

Having multiple tables in the FROM clause without the WHERE clause and JOIN would return repeated data from each table. For example, if the Employee table has two rows and the Department table has two rows, then the above query would return four rows where columns of one table would repeat for the columns of another table. You will learn more about it in the JOIN tutorial.