SELECT Statement In SQL

Introduction

In this article, we will learn about SQL SELECT statements. The SELECT statement retrieves data from a table in SQL Server.

SQL SELECT statement

SQL SELECT statement retrieves the data from one or more database tables. The SELECT statement can fetch all records or fetch a set of records based on a condition such as where, filter, sort, group by, etc.

Here are some of the use cases of the SQL SELECT statement.

Retrieving data from a single table or multiple tables
Filtering data based on conditions
Sorting data
Aggregating data
Grouping data
Joining tables
Limiting the number of rows returned

Method 1. Retrieving data from a single table or multiple tables

We can use the syntax below to retrieve the data from a table.

SELECT * FROM [Your_Table_Name];

In the above syntax, the * symbol retrieves all columns from a table (Enter table name in place of [Your_Table_Name]).

For example, I want to retrieve the data from a table named EmployeeDetail.

SELECT * FROM EmployeeDetail;

The above SQL statement retrieves all column data from the EmployeeDetail table, and the output looks like the following.

Note SELECT * is not recommended unless you need all the column's data. Use SELECT Column1, Column2 fromTable_Name format for better performance.

SELECT [Column1, Column2, Column3] FROM [Your_Table_Name];

The following SELECT statement uses column names to select data from a table. For example, I want to fetch the records for two columns only (EmpName and EmpAddress) from a table EmployeeDetail.

Select EmpName, EmpAddress from EmployeeDetail

The above SQL statement retrieves all data from two columns, and the output looks like the following.

Method 2. Filtering Data Based on Conditions

Filtering data based on conditions is a fundamental aspect of SQL (Structured Query Language). SQL allows you to specify conditions to filter the data you retrieve from a database, so you can retrieve only the data that meets specific criteria.

The basic syntax for filtering data in SQL is the WHERE clause, which comes after the FROM clause in a SQL query. The WHERE clause specifies a condition that must be true for a row to be included in the result set.

Here is an example of a SQL query that retrieves all rows from a table called "OrderDetail" where the value in the "OrderName" column is equal to "Apple":

SELECT * FROM OrderDetail WHERE OrderName = 'Apple';

The WHERE clause specifies the condition that the "OrderName" column must equal "Apple." Only rows that meet this condition will be included in the result set. In this statement, the "*" means that all columns should be included in the result set.

You can also use other operators in the WHERE clause, such as "<, ">", "<=", ">=", "!=", "<>", "LIKE", "IN", "NOT IN", "BETWEEN," and "NOT BETWEEN." These operators allow you to specify more complex conditions for filtering your data.

For example, here is a SQL query that retrieves all rows from a table called "OrderDetail," where the value in the "OrderDate" column is between January 1, 2021, and December 31, 2021:

  SELECT * FROM OrderDetail  WHERE OrderDate  BETWEEN '2021-01-01' AND '2021-12-31';

In this example, the "BETWEEN" operator is used to specify the range of dates for the "order_date" column.

Overall, filtering data based on conditions is an essential skill in SQL, and the WHERE clause is a powerful tool for selecting only the data you need from a database.

Output

Method 3. Sorting data

In SQL, you can sort data using the ORDER BY clause. The ORDER BY clause is used to sort the result set in ascending or descending order based on one or more columns.

The basic syntax for sorting data in SQL is as follows:

SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;

Here's an explanation of the various components of the syntax

SELECT Is used to select the columns that you want to retrieve from the table.
FROM Is used to specify the name of the table that you want to query.
ORDER BY Is used to sort the result set in ascending (ASC) or descending (DESC) order.
column1, column2, etc., are the names of the columns that you want to sort by.

For example, let's say we have a table called OrderDetail with columns OrderId, OrderName, OrderAddress, and OrderDate. We can retrieve all the rows in the table sorted by salary in descending order like this

SELECT OrderId,OrderName,OrderAddress,OrderDate FROM OrderDetail ORDER BY OrderName DESC

This will return all the rows in the employees table sorted by salary in descending order.

You can also sort by multiple columns by specifying more than one column in the ORDER BY clause. For example, if we wanted to sort the employees table by age in ascending order and then by salary in descending order, we could use the following SQL statement:

SELECT OrderId,OrderName,OrderAddress,OrderDate FROM OrderDetail ORDER BY OrderName DESC

This will return all the rows in the OrderDetail table sorted by age in ascending order and then by salary in descending order.

Output

Method 4. Aggregating data

Aggregating data in SQL involves combining and summarizing data from multiple rows or groups of rows in a table. SQL provides several built-in functions for aggregating data, including:

COUNT() This function returns the number of rows that match a specified condition.
SUM() This function returns the sum of a specified numeric column.
AVG() This function returns the average of a specified numeric column.
MIN() This function returns the minimum value of a specified column.
MAX() This function returns the maximum value of a specified column.

To use these functions to aggregate data in SQL, you can include them in your SELECT statement along with the GROUP BY clause, which groups the data by one or more columns. For example, the following SQL query finds the total number of customers for each country in the "Customers" table.

SELECT OrderName, COUNT(OrderId) as Orderdetails
FROM OrderDetail 
GROUP BY OrderName;

This query groups the data by the "OrderName" column and counts the number of rows for each group using the COUNT() function. The results are returned with the column headers "OrderName" and "OrderDetails."

Output

Method 5. Grouping data

Grouping data in SQL allows you to aggregate and summarize data based on one or more columns in a table. The most common way to group data in SQL is to use the GROUP BY clause.

Here is an example of how to group data in SQL: Suppose you have a table named "orders" with columns "order_id," "customer_id," "order_date," and "order_amount." To group the total order amounts by customer, you can use the following SQL query:

SELECT customer_id, SUM(order_amount) as total_order_amount
FROM orders
GROUP BY customer_id;

This query will group the orders table by customer_id and calculate the total order amount for each customer. The result will include two columns: customer_id and total_order_amount.

You can also group data by multiple columns. For example, if you want to group the total order amounts by customer and year, you can use the following SQL query:

SELECT customer_id, YEAR(order_date) as order_year, SUM(order_amount) as total_order_amount
FROM orders
GROUP BY customer_id, order_year;

This query will group the orders table by customer_id and order_year and calculate the total order amount for each combination of customer and year. The result will include three columns: customer_id, order_year, and total_order_amount.

Method 6. Joining tables

Joining tables in SQL is a fundamental operation for working with relational databases. A join operation combines rows from two or more tables based on a related column or set of columns.

Identify the tables you want to join and their relationship: Before joining tables, you need to understand their relationship. This is usually done by looking at the database schema or documentation. Here are the basic steps for joining tables in SQL
Choose the join type. Several types of join in SQL exist, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN. The choice of join type depends on the data you want to retrieve.
Specify the join condition. The join condition specifies how the tables are related. It typically involves matching values in a column or set of columns in each table.
Write the SQL statement: Once you have identified the tables, chosen the join type, and specified the join condition, you can write the SQL statement to perform the join.

Here is an example of a basic INNER JOIN operation

SELECT *
FROM OrderDetail
INNER JOIN OrderDetails
ON OrderDetail.OrderId =  OrderDetail.OrderId;

In this example, the INNER JOIN combines rows from table1 and table2 based on the matching values in the column of each table. The * symbol indicates that all columns from both tables should be included in the result set.

Output

Method 7. Limiting the number of rows returned

You can use the LIMIT keyword to limit the number of rows returned in SQL. The LIMIT keyword is followed by an integer value that specifies the maximum number of rows to return.

The syntax for using the LIMIT keyword is as follows.

SELECT column_name(s)
FROM table_name
LIMIT number_of_rows;

For example, to return the first ten rows from a table called users, you can use the following SQL query.


SELECT * FROM users LIMIT 10;

This will return only the first ten rows from the user's table.

Note that the syntax for using LIMIT may vary slightly depending on your SQL database management system.

Method 8. SELECT DISTINCT statement

The SELECT DISTINCT statement returns only DISTINCT(different) values.

Inside a table, a column may often contain many duplicate values; sometimes, you only want to list the different (distinct) values.

Syntax

SELECT DISTINCT column-name    
FROM table-name

The output looks like the following

Select distinct Empname,EmpAddress from EmployeeDetail

Method 9. SELECT FROM WHERE

SELECT WHERE statement is used to filter records.
SELECT WHERE statement is used to extract only those records that fulfill a specified condition.
SELECT WHERE statement is not used in the SELECT statement; it is used in UPDATE, DELETE statement, etc.

Example = > < >= <= <>

This is an example of equals (=) statement

Select EmpName from EmployeeDetail

This is an example of a greater than (>) statement

Select * from EmployeeDetail where Id >3

This is an example of a less than (<) statement

SELECT * FROM EmployeeDetail WHERE Id<3

This is an example of a greater than or equal (>=) statement

Select * from EmployeeDetail where Id >=2

This is an example of a less than or equal (<=) statement

Select * from EmployeeDetail where Id <=2

Less than greater than not equal (< >)

Note in some versions of SQL, this operator may be written as !=

This is an example of < > statement

Select * from EmployeeDetail where Id<> 4

Method 10. BETWEEN statement

The BETWEEN statement is used the BETWEEN a certain range for the SQL

The output looks like the following

Select * from EmployeeDetail where Id between 1 and 4

Method 11. Select LIKE statement

The LIKE operator is used in a WHERE clause statement to search for a specified pattern in a column in the SQL.

This is used in two wildcards, and it is often used in conjunction with the LIKE operator

% - The percent sign represents zero, one, or multiple characters
_ - The underscore represents a single character

The output looks like the following,

SELECT * FROM EmployeeDetail
WHERE EmpName LIKE 'a%';

Method 12. Select IN statement

The IN operator is used to specify multiple values in a WHERE clause.

The IN operator is a shorthand for multiple OR conditions in SQL.

The output looks like the following

Select * from EmployeeDetail where EmpCity In('Ghaziabad','Noida')

Method 13. SELECT HAVING

HAVING can be used only in the SELECT statement. HAVING is typically used with the GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.

Syntax

SELECT    
    select_list    
FROM    
    table_name    
GROUP BY    
    group_list    
HAVING    
    conditions;

The output looks like the following

Select COUNT(Id)FROM EmployeeDetail Having count(EmpCity)>4

Method 14. SELECT ORDER BY

The SELECT ORDER BY statement is the result set of a query by the specified column list and, optionally, limits the rows returned to a specified range. The order in which rows are returned in a result set is not guaranteed unless an ORDER BY clause is specified.

Syntax

SELECT column1, column2,   
FROM table_name    
ORDER BY column1, column2 ASC|DESC;

The output looks like the following

Select * from EmployeeDetail order by EmpName

Method 15. SQL SELECT AND, OR, NOT

SELECT AND statement

SELECT AND statement requires two conditions to be true in a WHERE statement.
SELECT WHERE statement with NOT negates the specified condition
SELECT AND statement: A WHERE statement with AND requires that two conditions are true

Syntax

SELECT column1, column2    
FROM table_name    
WHERE condition1 AND condition2 AND condition3;

The output looks like the following

Select * from EmployeeDetail where EmpName='Ravi' and EmpAddress='B-108 Sector109';

Method 16. SELECT OR statement

SELECT OR statement requires that one of two conditions are true in a where statement in the SQL.

Syntax

SELECT column1, column2,    
FROM table_name    
WHERE condition1 OR condition2 OR condition3;

The output looks like the following

Select * from EmployeeDetail where EmpName='Ravi' or EmpAddress='B-108 Sector109 Noida';

Method 17. SELECT NOT a statement

a WHERE statement with NOT negates the specified condition

The NOT operator displays a record if the condition(s) is NOT TRUE.

Syntax

SELECT column1, column2,    
FROM table_name    
WHERE NOT condition;

The output looks like the following

Select * from EmployeeDetail where NOT  EmpName='Ravi';

Method 18. SQL SELECT INSERT INTO

The SQL SELECT INSERT INTO Select statement copies data from one table and insert data into another table.

Syntax

INSERT INTO table2    
SELECT * FROM table1    
WHERE condition;

The output looks like the following


 insert into Client(ClientName,ClientAddress) Select EmpName,EmpAddress from EmployeeDetail

Method 19. Nested SQL SELECT statement

a subquery can be used anywhere where an expression is allowed in the Nested SQL SELECT statement

Syntax

SELECT column_name [, column_name ]      
FROM   table1 [, table2 ]      
WHERE  column_name OPERATOR      
   (SELECT column_name [, column_name ]      
   FROM table1 [, table2 ]      
   [WHERE])

The output looks like the following

Select(EmpName) from EmployeeDetail Inner Join Client on EmployeeDetail.Id=Client.Id

Method 20. SQL SELECT with CASE

The SQL SELECT with CASE statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

Syntax

CASE    
     WHEN condition1 THEN result1    
     WHEN condition2 THEN result2    
     WHEN conditionN THEN resultN    
     ELSE result    
 END;

The output looks like the following

Select EmpName,EmpAddress from EmployeeDetail order By(Case when EmpAddress is null then EmpName Else EmpName END);

Method 21. SQL SELECT with IF…THAN

The SQL SELECT with IF and THAN statement goes through conditions and returns a value when the first condition is met (like an IF-THEN-ELSE statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause.

If there is no ELSE part and no conditions are true, it returns NULL.

Syntax

IF <Condition>      
     {Statement | Block_of_statement}         
[ ELSE         
     {Statement | Block_of_statement}]

Why SQL * Is harmful?

The SQL * is harmful as of the following points

Unnecessary I/O
Increased network traffic
More application memory
Depends on the column order
Fragile views
Conflict in a join query
Risky while copying data.

Summary

In this article, we learned the basics of the SQL SELECT statement. We saw how to select all records from a database table.

FAQs

Q- Can you explain the difference between the DISTINCT keyword and the GROUP BY clause in a SELECT statement?
A- The DISTINCT keyword is used to eliminate duplicates from the result set of a SELECT statement. It applies to all columns selected, and it only shows the unique combinations of values across those columns. For example, if you have a table with a column called "color" and there are 10 rows where the color is "red," the DISTINCT keyword will only show "red" once in the result set.

The GROUP BY clause, on the other hand, is used to group rows that have the same values in one or more columns. It is often used in conjunction with aggregate functions (such as COUNT, AVG, MAX, MIN, and SUM) to group data and perform calculations on each group. For example, if you have a table with columns "category" and "price," you could use the GROUP BY clause to group all rows by category and then calculate the average price for each group.

Q- What is the ORDER BY clause in a SELECT statement, and how does it work?
A- The ORDER BY clause is used to sort the result set of a SELECT statement based on one or more columns. It follows the SELECT and FROM clauses and precedes the WHERE clause (if one is used). The basic syntax of the ORDER BY clause is

ORDER BY column1 [ASC|DESC], column2 [ASC|DESC]

Here, "column1" is the name of the first column to sort by, followed by "ASC" or "DESC" to specify whether the sorting should be in ascending or descending order. You can sort by multiple columns by separating them with commas.

6 Comments

Join the conversation! Your thoughts help the community grow.

Mageshwaran R7y ago
Great,very useful to me..
Onkar Sharma7y ago
Nice Article sir...
Pankajkumar Patel7y ago
Good one ...
Mahesh Chand7y ago
Good start. Nicely done!
Ashok Jangid7y ago
Very nice naresh....
Madan Shekar7y ago
Nice information