SQL Clauses are components that help filter, sort, group, or limit the results of a query. They are used with SQL commands to make data retrieval more precise and efficient.
1. WHERE
The WHERE clause is used to filter records that match a specific condition. It helps return only the rows you want.
SELECT column1, column2
FROM table_name
WHERE condition;Example:
SELECT * FROM students
WHERE age > 18;Returns only students whose age is greater than 18.
2. ORDER BY
The ORDER BY clause sorts the results in ascending (ASC) or descending (DESC) order.
SELECT column1, column2
FROM table_name
ORDER BY column_name ASC|DESC;Example:
SELECT * FROM students
ORDER BY name ASC;Displays all students sorted alphabetically by name.
3. GROUP BY
The GROUP BY clause groups rows that have the same value in specified columns. It is mostly used with functions like COUNT(), SUM(), etc.
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1;Example:
SELECT grade, COUNT(*)
FROM students
GROUP BY grade;Shows how many students are in each grade.
4. HAVING
The HAVING clause is like WHERE, but it is used to filter results after grouping with GROUP BY.
SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;Example:
SELECT grade, COUNT(*)
FROM students
GROUP BY grade
HAVING COUNT(*) > 3;Shows only grades with more than 3 students.
5. LIMIT
The LIMIT clause is used to restrict the number of rows returned by the query.
SELECT column1, column2
FROM table_name
LIMIT number;Example:
SELECT * FROM students
LIMIT 5;Shows only the first 5 rows from the table.
6. DISTINCT
The DISTINCT clause ensures that duplicate values are removed from the result.
SELECT DISTINCT column_name
FROM table_name;Example:
SELECT DISTINCT grade
FROM students;Lists each unique grade only once.
7. BETWEEN
The BETWEEN clause is used to filter the result set within a specific range (inclusive of boundaries).
SELECT column1, column2
FROM table_name
WHERE column_name BETWEEN value1 AND value2;Example:
SELECT * FROM students
WHERE age BETWEEN 18 AND 22;Returns students aged between 18 and 22.
8. IN
The IN clause checks whether a value exists in a set of values.
SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, ...);Example:
SELECT * FROM students
WHERE grade IN ('A', 'B');Returns students who have either grade ‘A’ or ‘B’.
9. LIKE
The LIKE clause is used to search for a pattern in a column using wildcards (%, _).
SELECT column1, column2
FROM table_name
WHERE column_name LIKE pattern;Example:
SELECT * FROM students
WHERE name LIKE 'J%';Finds students whose names start with ‘J’.
10. IS NULL / IS NOT NULL
These are used to test if a column contains NULL (missing value) or not.
SELECT column1, column2
FROM table_name
WHERE column_name IS NULL;
-- OR
SELECT column1, column2
FROM table_name
WHERE column_name IS NOT NULL;Example:
SELECT * FROM students
WHERE grade IS NULL;Returns students who have not been assigned a grade.