SQL SELECT Statement

SQL SELECT Statement: Now that we have a better understanding of inserting data into our database table, let’s move on to selecting the information stored in existing tables using an SQL query.

When we think about some frequently used SQL commands, SQL Statement is one of those commands. The SELECT statement allows you to retrieve data from one or more tables. It can be used for a full table retrieval to get only the rows meeting certain conditions and/or combinations of conditions.

Let’s examine the syntax of SELECT statements in different databases, explore their features and review some examples.

Syntax

Here is the syntax for simple basic SQL queries:

SELECT column1_name, column2_name, columnN_name FROM table_name;

The names of the columns or fields in a database table that you want to retrieve values for can be specified as column1_name, column2_name, … If you desire to get all the values from a table, then use this syntax:

SELECT * FROM table_name;

Let’s take an example of How to retrieve all: the EMPLOYEE table records.

select * from EMPLOYEE;

When you execute the above SQL query, it will display all the records of the EMPLOYEE Table. You can download the SQL query if you want to practice with the below EMPLOYEE Table.

EMPLOYEE Table

The asterisk (*) is a wildcard symbol that stands for everything. As shown in the SELECT statement, it can be used as an abbreviation for all the rows and columns in the employees’ table.

Select Columns from Table

If you don’t want to see all the table records, you want to see some specific columns, and then you can run the below query. For Example, if you want to display only the EMPNO, ENAME, and DEPTNAME, then the query will be:

Select EMPNO, ENAME, DEPTNAME from EMPLOYEE;

The result will be like this:

SQL SELECT Statement 1

While retrieving the records, we can also use the Select SQL Statement in various other formats; we have mentioned those in detail:

We will try to understand each of those in detail with the syntax and examples.

SQL SELECT UNIQUE

SELECT UNIQUE is an outdated syntax once used in Oracle, but ANSI standard replaced it with the keyword DISTINCT. Despite this change, Oracle kept SELECT UNIQUE to ensure backward compatibility. In short, the statement can be used to get a single or distinct element from a table.

But if you try to run the SELECT UNIQUE, you will get the syntax error below.:

SELECT UNIQUE  DEPTNAME  
FROM EMPLOYEE; 
SQL SELECT Statement 2

SQL DISTINCT Or SQL SELECT DISTINCT

The SELECT keyword in SQL can be used with the DISTINCT command to get unique data. There may often be multiple duplicates in a single table, but sometimes you want to see the unique values from a database table when you run the queries. In these instances, the special SQL SELECT DISTINCT statement is utilized.

The basic syntax for the SQL SELECT DISTINCT statement is as follows:

SELECT DISTINCT column_name1, column_name2,...
FROM table_name;

Note: SQL SELECT UNIQUE and SQL SELECT DISTINCT statements are the same.

Examples of SQL DISTINCT Statements

Find the Unique department from the Employee table.

SELECT DISTINCT  DEPTNAME  
FROM EMPLOYEE;
SQL SELECT Statement 3

SQL SELECT COUNT

We can use the SQL COUNT function with the SQL SELECT statement. When we execute the SQL COUNT() function, it will return the number of records of the table as output because the count SQL is an aggregate function.

SQL COUNT OF ROWS

Suppose there is a requirement to find the total number of records; then, in that case, we can use the SQL COUNT function.

Syntax

SELECT COUNT(column_name) FROM table_name;  
SQL SELECT Statement 4

After executing the query, we are getting the results as 14 because we have 14 records in the table. But if the mentioned column has null values, then the count() function will exclude the null values.

To understand, we will take the example of column values.

SELECT COUNT(MGR) FROM EMPLOYEE;  
SQL SELECT Statement 5

The query’s output is 11 because the MGR column has three null values. So the count function excludes those records and shows only 11 in output.

But if we want to include the null values into the count, then in that case, we need to take the help of wildcard (*). For that, we can write the query below:

SELECT COUNT(*) FROM EMPLOYEE; 
SQL SELECT Statement 6

SQL COUNT UNIQUE

When we have a requirement to find the unique values and also count the total number of values, then in that case, we can use the SQL COUNT UNIQUE, which means SQL COUNT DISTINCT. Because when we were discussing SQL Unique, we mentioned Unique is no longer supported. That’s why we have taken the help of the DISTINCT.

Syntax:

Here is the syntax for the SQL COUNT UNIQUE

SELECT COUNT(DISTINCT column_name) FROM table_name WHERE [condition];  

For Example, We have to find the total number of the unique department in the Employee Table.

SELECT COUNT (DISTINCT DEPTNAME) 
AS "NUMBEER OF DEPT" FROM EMPLOYEE;
SQL SELECT Statement 7

SQL COUNT HAVING

The SQL COUNT() function can be used with a HAVING clause instead of the WHERE clause to set a condition when selecting data.

A GROUP BY with a HAVING clause can be used to get results for a particular group of values in one column based on conditions specified by the HAVING clause.

Let’s see how we can use the SQL COUNT HAVING in our real-time example. So to better understand the SQL COUNT HAVING, we will use the EMPLOYEE table.

Requirement: We need to find those departments from the EMPLOYEE table which appear more than three times in the DEPTNAME column.

SELECT DEPTNAME, COUNT(DEPTNAME) as COUNT
FROM EMPLOYEE
GROUP BY DEPTNAME
HAVING COUNT(DEPTNAME) > 3;
SQL SELECT Statement 8

Here is the graphical representation:

SQL COUNT HAVING Diagram PNG

SQL SELECT TOP

SQL’s SELECT TOP statement retrieves a limited number of records or rows from the database table. The TOP clause in the command also determines how many rows are returned. This sql select top command is mainly used when in the table there are thousands of records, but out of which, we want some specific number of records. And when we execute the sql select top command, it will retrieve and show the mentioned N number of records.

Syntax For SQL SELECT TOP

SELECT TOP number | percent column_Name1, column_Name2, ....., column_NameN  
FROM table_name 
WHERE [Condition] ; 

Not all databases can use the TOP keyword to select a limited number of entries. Oracle utilizes ROWNUM, and MySQL uses the LIMIT keyword.

SQL SELECT TOP Examples:

Suppose you want to display the top 3 records EMPNO, ENAME, and DEPTNAME of the employee table

SELECT TOP 3 EMPNO, ENAME, DEPTNAME FROM EMPLOYEE;
SQL SELECT Statement 9

Similarly, if you want to display or retrieve the top 2 records whose DEPTNAME is Banking, then the query will be.

SELECT top 2 * FROM EMPLOYEE where DEPTNAME='BANKING';
SQL SELECT Statement 10

Think about one scenario where you need to display the 50 percent records of the table; in that case, the query will look like the below:

SELECT TOP 50 PERCENT * FROM EMPLOYEE;
SQL SELECT Statement 11

SQL SELECT TOP 10

When a candidate goes for an interview, in most interviews, you may face one interview question: How to find the Top 10 records from a table? And for this question, here is the answer, we can take the help of the SQL SELECT TOP 10 command.

SELECT TOP 10 * FROM EMPLOYEE; 
SQL SELECT Statement 12

SQL SELECT FIRST

The SELECT FIRST statement allows you to retrieve the first value from a specified column in an existing table. Here is the syntax for the SQL SELECT FIRST command:

SELECT FIRST(column_name) FROM table_name;  

SELECT FIRST statement work in the MS Access database only

As this command works only in MS Access, but If we try to use the above syntax in SQL Server Management Studio, then we get the below error message:

SQL SELECT Statement 13

But we can fulfill our requirement with the help of SQL SELECT TOP commands like the one below. For Example, we require to display the first Name of the employee, then here, the SQL Query

SELECT TOP 1 ENAME FROM EMPLOYEE;
SQL SELECT Statement 14

SQL SELECT LAST

In the Structured Query Language, we have many functions to deal with the databases, and the Last() function is one of them. This Last() function retrieves the last value of an existing table’s mentioned column.

Here is the syntax:

SELECT LAST (Field_Name) FROM Table_Name ;  

In MySQL, Oracle, and SQL servers, the LAST function cannot be used to retrieve the last record. However, there are workarounds available for these databases which can help you get access to that data.

For Example, let’s get the last employee name of the employee table in SQL SERVER.

SELECT TOP 1 ENAME FROM EMPLOYEE
ORDER BY EMPNO DESC;
SQL SELECT Statement 15

SQL SELECT IN STATEMENT

Using the IN operator in SQL with the WHERE command makes it simple to check if a column is equal to any of several given values. Instead of needing to use OR multiple times, this method can quickly and easily verify whether or not there is an exact match between a value from one set and another.

The Syntax for the SQL SELECT IN STATEMENT is

Expression IN (value 1, value 2 ... value n);  

Example Of SQL SELECT IN STATEMENT

Get all the details of SMITH and KING employees from the EMPLOYEE table.

SELECT * FROM EMPLOYEE 
WHERE ENAME IN ('SMITH','KING');
SQL SELECT Statement 16

SQL SELECT MULTIPLE

Various methods exist to select multiple rows and columns from an existing table. Below are the syntaxes that can easily grab several columns and rows from your current table!

Here is the syntax:

SELECT column1,column2, …, columnN
FROM table_name; 

Fetch employee details (EMPNO, ENAME, SAL, DEPTNAME) from the EMPLOYEE table. The query was as follows:

SELECT EMPNO, ENAME, SAL, DEPTNAME FROM EMPLOYEE;
SQL SELECT Statement 17

Get all the records of the EMPLOYEE table.

SELECT * FROM EMPLOYEE;
SQL SELECT Statement 18

SQL SELECT DATE

Using SELECT DATE in Microsoft SQL Server can help you retrieve data from a table related to the date. By default, dates appear with the format ‘YYYY-MM-DD’.

SELECT * FROM table_name
WHERE condition1, condition2,..;

To understand, let us try an example:

Get the records of the employee whose HIREDATE is ‘1981-11-17’

SELECT * FROM EMPLOYEE
WHERE HIREDATE='1981-11-17';
SQL SELECT Statement 19

SQL SELECT SUM

The SQL SUM() function can be used in a query to return the sum of an expression. This is helpful for quickly finding totals and aggregations based on data stored in your database.

For SQL SUM(), Here is the syntax:

SELECT SUM (expression)  
FROM tables  
WHERE conditions;  

Let’s see some of the examples to understand these concepts clearly:

SELECT SUM (SAL) AS "Total Salary"  
FROM EMPLOYEE;
SQL SELECT Statement 20

SQL SELECT NULL

If a column in a table does not have an assigned value, it is represented by NULL. This is distinct from zero or empty spaces. When inserting and updating rows, if optional columns are not included in the INSERT statement, they will automatically be given the NULL value as their default.

Syntax Of SQL SELECT NULL

SELECT column_names
FROM table_name
WHERE column_name IS NULL; 

To determine whether the column value is NULL, we can use “IS NULL” or “IS NOT NULL”. It’s important to note that comparing a NULL value with operators such as =, >, and < is impossible.

Syntax For IS NULL

SELECT column_names
FROM table_name
WHERE column_name IS NULL; 

Syntax For IS NOT NULL

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

Now we will see how to implement the “IS NULL” or “IS NOT NULL”.

IS NULL Example:

Get all the records whose mgr values are null.

SELECT * FROM EMPLOYEE 
WHERE MGR IS NULL;
SQL SELECT Statement 21

Get all the records whose mgr values is null.

SELECT * FROM EMPLOYEE 
WHERE MGR IS NOT NULL;
SQL SELECT Statement 22

SQL SELECT WHERE STATEMENT

The WHERE clause in SQL helps specify a condition when extracting data from one or more tables. It will return the specified value only if the given requirement is met; therefore, it can filter out records you don’t need and obtain just what you require.

The WHERE clause is used in the SELECT statement and can also be found in the UPDATE and DELETE statements. We will take a closer look at these occurrences later on.

Syntax For the SQL SELECT WHERE STATEMENT

SELECT column1, column2, columnN 
FROM table_name
WHERE [condition]

You can specify a condition using the comparison or logical operators like >, <, =, LIKE, NOT, etc.

Here we will see some examples to understand how to use the SQL SELECT WHERE STATEMENT.

Example 1: Retrieve the records where the employee’s name is Scott.

SELECT * FROM EMPLOYEE 
WHERE ENAME ='SCOTT';
SQL SELECT Statement 23

Example 1: Get all employees with a salary of more than 2400.

SELECT * FROM EMPLOYEE 
WHERE SAL>2400;
SQL SELECT Statement 24

SQL SELECT AS

The SELECT AS statement can assign a temporary alias name for a column or table to provide security for the original table. This is referred to as aliasing and will remain effective until the completion of the SELECT statement. Using this with your Select Statement, you can create alternate names, making it simpler when referring back to them during database queries.

In Short, an SQL AS statement is used to create an alias for a column name or table. We can use the SQL AS with the select statement.

SQL SELECT AS Syntax For Column

SELECT column_name1 AS alias_name1,
column_name2 AS alias_name2,..., 
column_nameN AS alias_nameN
FROM table_name; 

SQL AS Syntax For Table

SELECT column_name1,
column_name2,..., 
Column_nameN
FROM table_name AS alias_table_name; 

Now we will see some examples of SQL SELECT AS statements to understand the implementation of SQL AS.

Example 1: Display ENAME as Name

SELECT EMPNO, ENAME AS NAME, HIREDATE, SAL, DEPTNAME
FROM EMPLOYEE;
SQL SELECT Statement 25

Example 2: Using the Sql Select As with multiple columns

SELECT EMPNO, ENAME AS NAME, HIREDATE, SAL as SALARY, DEPTNAME
FROM EMPLOYEE;
SQL SELECT Statement 26

Example 3: SQL AS with SQL Expression

You can use the CONCAT() function to combine data from multiple columns and present it in a single column. As an example,

SELECT CONCAT(EMPNO,' ', ENAME) AS EMPLOYEE_DETAILS,DEPTNAME
FROM EMPLOYEE;
SQL SELECT Statement 27

Example 4: Use of SQL AS with the table names

SQL SELECT Statement 28
SQL SELECT Statement 29

Here is an example of a Table alias:

SELECT C.customer_id, C.first_name, C.AGE, O.order_date 
FROM CUSTOMERS AS C, ORDERS AS O
WHERE  C.customer_id = O.order_id
SQL SELECT Statement 30

Key Points about SQL AS

  • A temporary alias allows renaming a column or table in a SQL SELECT statement, but this change only lasts until the end of the statement’s execution.
  • Once the SELECT statement is completed, the temporary alias is discarded, and the column’s or table’s original name is restored.
  • Typically, only a database administrator (DBA) has the necessary privileges to create temporary aliases in real-world projects.
  • The temporary alias is also known as a correlation name, another term for a temporary name given to a table or column within the context of a single SQL statement.

SQL SELECT DATABASE

Suppose database users and administrators want to carry out operations on tables, views, and indexes with the current SQL database. In that case, they should begin by choosing the database in which they plan to run their queries.

We have to use the USE statement to select a database in SQL SEVER. Here is the syntax:

USE DatabaseName;

Before Selecting any database, we need to see all the database present. So to check that, we can use the below command:

select * from sys.databases;

Now we are trying to change databases from one to another.

USE master;
SQL SELECT Statement 31

Conclusion:

In this post, we are trying to cover all the possible uses of SQL SELECT statements and their various uses. If you want to add or report something about this post, we can inform you in the comment section.

Avatar for Softwaretestingo Editorial Board

I love open-source technologies and am very passionate about software development. I like to share my knowledge with others, especially on technology that's why I have given all the examples as simple as possible to understand for beginners. All the code posted on my blog is developed, compiled, and tested in my development environment. If you find any mistakes or bugs, Please drop an email to softwaretestingo.com@gmail.com, or You can join me on Linkedin.

Leave a Comment