Filtering and sorting are essential when working with SQL queries. By combining the logical and comparison operators, and sorting techniques, you can retrieve only the data you need and present it in a meaningful order.
WHERE Clause - Data Filtering in SQL
The WHERE clause filters rows based on specific conditions. It is one of the most commonly used SQL filtering techniques.
This statement is used to select the employees which have value of department_id as 1.
Practice: Select all the rows from the projects table with project_id of 3.
SQL LIKE Operator & Wildcards
The LIKE operator is used for pattern matching in SQL.
- % represents zero or more characters.
- _ represents a single character.
This statement is used to select employees whose job title contains “Engineer” anywhere in the text.
This will match job titles like "Engineer", "Tngineer", or "Vngineer", but not "Senior Engineer" because _ only replaces one character at the start.
Practice: Select all the rows from the projects table which contains "Campaign" in the project_name field.
Logical Operators (AND, OR, NOT)
AND: All conditions must be true.
This statement retrieves all employees who work in department 1 and have a job title containing the word "Engineer". Both conditions must be met for a record to be included in the result.
Practice: Select all the rows from the projects table with project_id of 1 and employee_id of 1.
OR: At least one condition must be true.
This statement returns all employees who belong to either department 1 or department 3. If an employee meets at least one of the conditions, they will appear in the result.
Practice: Select all the rows from the projects table with project_id of 4 or project_name "Website Redesign".
NOT: Excludes records that match the condition.
This statement retrieves all employees except those in department 2. It filters out any record where the department_id equals 2.
Practice: Select all the rows from the projects table except with project_id of 5.
Comparison Operators
Operator | Description | Example
|
= | Equal to | WHERE department_id = 1 |
!= or <> | Not Equal to | WHERE job_title != 'Manager' |
< | Less than | WHERE hire_date < '2018-07-22' |
> | Greater than | WHERE hire_date > '2022-09-12' |
<= | Less than or Equal to | WHERE hire_date <= '2018-07-22' |
>= | Greater than or Equal to | WHERE hire_date >= '2022-09-12' |
SQL IN & NOT IN Operators
The IN operator checks if a value matches any value in a given list.
This statement selects employees in departments 1 or 3.
Practice: Select all the rows from the projects table with project_id of 3 or 4.
This statement selects employees which are not in departments 2 or 4.
Practice: Select all the rows from the projects table except with project_id of 1 or 2.
SQL BETWEEN Operator
BETWEEN is used for filtering values within a range, inclusive of both ends.
This statement retrieves employees hired between January 1, 2019 and December 31, 2020.
Practice: Select all the rows from the projects table with start_date between January 10, 2023 and January 1, 2024.
Sorting Data with ORDER BY in SQL
ORDER BY sorts the results in ascending (ASC) or descending (DESC) order.
This statement selects employees from most recently hired to earliest hired.
Practice: Select all the rows from the projects table with most recent start_date.
This statement selects employees from most earliest hired to recently hired.
Practice: Select all the rows from the projects table with least recent start_date.
Removing Duplicates with SQL DISTINCT
DISTINCT returns only unique values, removing duplicates from the result set.
This statement displays each unique job title in the employees table.
Practice: Select all the rows from the employees table with unique department_id.
LIMIT in SQL
LIMIT is used in SQL to control how many rows are returned in the result set of a query. It’s especially useful when working with large datasets where you don’t want to retrieve every row at once.
This statement displays only the first 3 rows of the employees table.
Practice: Select only 2 rows from the projects table.
OFFSET in LIMIT
Skip the first N employees and show the next.
This query will skip the first 2 rows because OFFSET value is 2 and then display next 2 rows.
Practice: Select only 2 rows from the projects table with an offset of 2.
FAQs
1. What is the difference between WHERE and HAVING in SQL?
WHERE filters rows before grouping and aggregation.
HAVING filters rows after a GROUP BY operation.
2. When should I use IN vs OR in SQL queries?
IN is shorter and more efficient for checking against a list of values.
OR can be used but becomes harder to read with many conditions.
Using OR: SELECT * FROM employees WHERE department_id = 1 OR department_id = 3;
Using IN: SELECT * FROM employees WHERE department_id IN (1, 3);
3. What is the difference between DISTINCT and GROUP BY in SQL?
DISTINCT removes duplicate rows and shows only unique results.
GROUP BY groups rows based on column values and is often used with aggregate functions like COUNT, SUM, AVG.
4. How does ORDER BY affect query performance?
Sorting with ORDER BY can slow down queries on large datasets because the database must arrange results. Adding indexes to the sorting column can improve performance significantly.
5. What is the difference between LIMIT and OFFSET in SQL?
LIMIT specifies how many rows to return.
OFFSET tells SQL how many rows to skip before starting to return rows.
They are often used together for pagination, e.g., showing 10 rows per page.