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.
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:
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 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 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;
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;
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 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 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;
Here is the graphical representation:
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;
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';
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 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 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:
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 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 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 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;
Get all the records of the EMPLOYEE table.
SELECT * FROM EMPLOYEE;
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 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 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;
Get all the records whose mgr values is null.
SELECT * FROM EMPLOYEE WHERE MGR IS NOT NULL;
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';
Example 1: Get all employees with a salary of more than 2400.
SELECT * FROM EMPLOYEE WHERE SAL>2400;
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;
Example 2: Using the Sql Select As with multiple columns
SELECT EMPNO, ENAME AS NAME, HIREDATE, SAL as SALARY, DEPTNAME FROM EMPLOYEE;
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;
Example 4: Use of SQL AS with the table names
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
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;
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.
