Blog

SQL SELECT Operator

An SQL Select statement or operator is used to select data from one or more tables. When the SELECT query gets executed, a resulting dataset that is returned is called the result-set. We can select a single column or multiple columns at once when executing this statement.

Below is the syntax of the basic SELECT statement.

SELECT 
    column1, column2 ..
FROM
    table_name;

Here column1,column2 are the column names that we want to select from a given table. If we want to select all the columns from the given table, we can use the below syntax.

SELECT * FROM table_name;

Here is, the wildcard expression * means that we want to select all the columns from the table.

The examples used in this tutorial are based on this SQL file that you can find on my GitHub page.

Using SELECT to query all the columns from the table

Let’s take the example of the Customer table and select all the columns.

SELECT * FROM OT.CUSTOMERS_DATA ;

This query will return the below output.

CUS_ID  NAME    ADDRESS WEBSITE CRDT_LMT
101 State Street Corp.  1607 Abwdrts St, Bangalore, Kar http://www.statestreet.com  3,500
102 Unum Group  1608 Amadeus St, Bangalore, Kar http://www.unum.com 3,500
103 Reynolds American   1610 Betrayal Crt, Bangalore, Kar   http://www.reynoldsamerican.com 5,000
104 Group 1 Automotive  1614 Crackers Rd, Bangalore - India, Kar    http://www.group1auto.com   2,300
105 Henry Schein    1615 Crackers Crt, Bangalore - India, Kar   http://www.henryschein.com  5,000
106 Norfolk Southern    1618 Footloose St, Bangalore - India, Kar   http://www.nscorp.com   900
107 Reinsurance Group of America    1619 Footloose Rd, Bangalore - India, Kar   http://www.rgare.com    900
108 Public Service Enterprise Group 1621 Gargon! Blvd, Bangalore - India, Kar   http://www.pseg.com 600
109 DTE Energy  8110 Jackson Rd, Ann Arbor, MI  http://www.dteenergy.com    200
110 Assurant    101 N Falahee Rd, Jackson, MI   http://www.assurant.com 300

Using SELECT to query specific columns from the table

Now, let’s see how we can select only certain columns from a table. In the below query, we are selecting three columns called Customer Identification, Customer Name, and Credit Limit identified by CUS_ID, NAME, CRDT_LMT columns from the Customer data table.

SELECT CUS_ID, NAME, CRDT_LMT FROM OT.CUSTOMERS_DATA;

Below is the output of the above query, which only returns three columns as part of the result set.

CUS_ID  NAME    CRDT_LMT
101 State Street Corp.  3,500
102 Unum Group  3,500
103 Reynolds American   5,000
104 Group 1 Automotive  2,300
105 Henry Schein    5,000
106 Norfolk Southern    900
107 Reinsurance Group of America    900
108 Public Service Enterprise Group 600
109 DTE Energy  200
110 Assurant    300

Perform Transformation on Columns while Selecting the data

When we use SELECT statement in SQL, we can also do some transformation on certain columns while selecting the data. In the below query, we will make the NAME column values to the upper case while selecting and using an alias to denote the newly selected column.

SELECT CUS_ID, UPPER(NAME) AS NAME FROM OT.CUSTOMERS_DATA;
CUS_ID  NAME
101 STATE STREET CORP.
102 UNUM GROUP
103 REYNOLDS AMERICAN
104 GROUP 1 AUTOMOTIVE
105 HENRY SCHEIN
106 NORFOLK SOUTHERN
107 REINSURANCE GROUP OF AMERICA
108 PUBLIC SERVICE ENTERPRISE GROUP
109 DTE ENERGY
110 ASSURANT

If we don’t use the alias AS NAME, as mentioned in the above query, the transformed column name will be UPPER (NAME). This will create problems when you are transforming some data while selecting and inserting it into another column, as the SQL engine will complain, saying no valid column is found.

The below result is returned when we don’t use an alias.

CUS_ID  UPPER(NAME)
101 STATE STREET CORP.
102 UNUM GROUP


Common clauses used with SELECT query in SQL

Below are the common SQL clauses that are most commonly used with the Select Statement.

  • WHERE: It filters table records that are needed to be based on certain conditions. This clause cannot be used to filter aggregated records.
  • ORDER BY: It sorts the table records based on some field(s) in ascending (ASC) or descending order (DESC).
  • GROUP BY: It groups table records with identical data and can be used in conjunction with some aggregation functions to produce summarized results from the database.
  • HAVING: It is used to filter table records in combination with the GROUP BY clause. It is mainly used to filter aggregated records, like finding duplicates.
Tags: