SELECT Query in SQL : Fetch Data Using WHERE, ORDER BY & GROUP BY

Published : 06 July 2024
Updated : 06 July 2024

181 Views

SQL (Structured Query Language) is the cornerstone of database management. It allows users to retrieve and manipulate data stored in relational databases. that means SQL allows users to perform CURD operation on database. CURD is fundamental operation of SQL.

Among these fundamental operations, SELECT query is used to fetch data from database. you can specify criteria of fetching data , can also apply some other operations like grouping , ordering etc.

Syntax

SELECT column1, column2, ......
FROM table_name;

SELECT Clause is used to specifies the columns to be retrieved from the database table.
FROM Clause is used to specifies the tables from which to retrieve data.

Lets assume we have 2 tables `Users` and `UserAddress`. Users has Id, Name , Mobile ,DOB and UserAddress has Id, UserId, AddressType, Address , Country , City and PostalCode colums

CREATE TABLE Users (
    Id INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL,
    Mobile VARCHAR(15) NOT NULL UNIQUE,
    DOB DATE NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
// ---------------------------------------------------------------
CREATE TABLE UserAddress (
    Id INT AUTO_INCREMENT PRIMARY KEY,
    UserId INT NOT NULL,
    AddressType ENUM('Home', 'Office', 'Other') NOT NULL,
    Address VARCHAR(255) NOT NULL,
    Country VARCHAR(100) NOT NULL,
    City VARCHAR(100) NOT NULL,
    PostalCode VARCHAR(20) NOT NULL,
    CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP,
    UpdatedAt DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT FK_UserAddress_Users
        FOREIGN KEY (UserId)
        REFERENCES Users(Id)
        ON DELETE CASCADE
);

Selecting all data and all columns from Users table

If you want to fetch all columns from a table you can use * . This will fetch all columns from a table without specifying columns name in query.

SELECT * FROM Users;

Selecting specifies columns from Users table.

If you want to fetch specifies columns from a table you have to mention columns name in sql SELECT query.

SELECT Id, Name FROM Users;

SELECT with WHERE

if you want to fetch data from table based on certain condition. you have to use WHERE clause. in where clause you can specify your condition.

SQL query to fetch users which mobile number start with 987.

SELECT Id, Name, Mobile FROM Users WHERE Mobile like "987%";

SELECT with ORDER BY

if you want to fetch data from table in ascending or descending order . you have to use ORDER BY clause. The ORDER BY clause sort the result set based on one or more columns, either in ascending (ASC) or descending (DESC) order that you specify in query. Default order of fetching data is ASC.

SQL query to fetch users that are sorted in name as ascending and DOB as descending order.

SELECT Id, Name, DOB FROM Users ORDER BY Name ASC, DOB DESC;
                               OR
SELECT Id, Name, DOB FROM Users ORDER BY Name , DOB DESC;

SELECT with LIMIT

if you want to fetch n rows of data from table. you should use LIMIT clause.

SQL query for fetching 4 users.

SELECT Id, Name FROM Users LIMIT 4;

SELECT with JOIN

if you want to fetch data from different tables. you should use JOIN clause.

SQL query for fetching users which Permanent address is India.

SELECT u.Id, u.Name, ua.Address, ua.AddressType, ua.Country 
FROM Users as u 
JOIN UserAddress as ua on u.Id=ua.UserId
WHERE AddressType = "Pre" and Country = "india";

SELECT with Multiple Clause

if you want to fetch data where you need to apply multiple clause. you can write a single query with multiple clause based on your requirement below is the simple query structure of such cases.

SELECT column1, column2,...... 
FROM table_name 
WHERE columnX like "___" 
order by columnY 
LIMIT n;

Frequently Asked Questions (FAQs)

1. What is the SELECT query in SQL?

The SELECT query is used to retrieve data from one or more tables in a database. It allows you to specify which columns you want, apply conditions to filter rows, sort results, group data and limit the number of records returned. It is the most frequently used SQL command and forms the foundation of data querying.

2. Why is the SELECT query considered the most important SQL command?

SELECT is the most important SQL command because almost every application depends on reading data. Whether it’s displaying user details, generating reports or performing analytics, SELECT queries are used everywhere. Without SELECT, stored data would not be accessible in a meaningful way.

3. What does SELECT * mean and should it be avoided?

SELECT * retrieves all columns from a table. While it is useful for quick testing or exploration, it should generally be avoided in production queries. Fetching unnecessary columns increases memory usage, network overhead and can negatively impact performance, especially on large tables.

4. How is SELECT different from other SQL commands like INSERT or UPDATE?

SELECT is a read-only operation that fetches data without modifying it. INSERT, UPDATE and DELETE are write operations that change data. Because SELECT does not alter data, it is safer and more commonly used, but poorly written SELECT queries can still impact performance.

5. What is the purpose of the WHERE clause in a SELECT query?

The WHERE clause filters rows based on conditions you define. It ensures that only relevant records are returned instead of the entire table. Using WHERE efficiently reduces unnecessary data processing and improves query performance, especially in large datasets.

6. How does the ORDER BY clause work in SELECT queries?

ORDER BY sorts the result set based on one or more columns. By default, sorting is done in ascending order. You can explicitly specify ascending or descending order for each column. ORDER BY is often used in reports and UI displays where data needs to appear in a meaningful sequence.

7. What happens if ORDER BY is not used in a SELECT query?

If ORDER BY is not specified, the database does not guarantee any specific order of results. The data may appear ordered by chance, but this should never be relied upon. Always use ORDER BY when the order of rows matters.

8. What is the LIMIT clause used for?

LIMIT restricts the number of rows returned by a SELECT query. It is commonly used for pagination, previews and performance optimization. Fetching only required rows helps reduce server load and speeds up query execution.

9. Can SELECT queries retrieve data from multiple tables?

Yes. SELECT queries can retrieve data from multiple tables using JOIN clauses. JOIN allows you to combine related data across tables based on common columns, making SELECT extremely powerful for relational data analysis.

10. What are JOINs and why are they important in SELECT queries?

JOINs allow SELECT queries to combine data from multiple tables based on relationships. They are essential in normalized databases where related data is stored in separate tables. JOINs help fetch meaningful combined results without duplicating data.

11. Can SELECT queries modify data in any way?

No. SELECT queries do not modify data directly. However, complex SELECT queries can be resource-intensive and impact performance if not optimized. This is why indexing and proper filtering are important even for read-only operations.

12. How do SELECT queries impact database performance?

Poorly written SELECT queries can slow down the database by scanning large tables unnecessarily. Using proper WHERE conditions, indexes, selecting only required columns and limiting result sets helps maintain good performance and scalability.

13. Is SELECT query commonly asked in SQL interviews?

Yes. SELECT queries are among the most common interview topics in SQL. Interviewers test understanding of filtering, sorting, joins, performance optimization and real-world query scenarios rather than just basic syntax.

14. Can SELECT queries be nested inside other SELECT queries?

Yes. SELECT queries can be nested as subqueries. Subqueries allow you to perform complex logic, such as filtering results based on aggregated data or results from another query. They are powerful but should be used carefully to avoid performance issues.

15. What are common mistakes developers make with SELECT queries?

Common mistakes include using SELECT * unnecessarily, missing WHERE clauses, ignoring indexes, assuming implicit ordering and writing overly complex queries without optimization. Understanding how SELECT works internally helps avoid these issues and leads to better database performance.