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.
- WHERE:- The WHERE clause is used to filter records that match a specific condition. It helps return only the rows you want.
Syntax:-
SELECT column1, column2
FROM table_name
WHERE condition;
Example:
SELECT * FROM students
WHERE age > 18;
→ Returns only students whose age is greater than 18.
- ORDER BY:- The ORDER BY clause sorts the results in ascending (ASC) or descending (DESC) order.
Syntax:-
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.
- GROUP BY:- The GROUP BY clause groups rows that have the same value in specified columns. It’s mostly used with functions like COUNT(), SUM(), etc.
Syntax:-
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.
- HAVING:- HAVING is like WHERE, but it is used to filter results after grouping with GROUP BY.
Syntax:-
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.
- LIMIT:- The LIMIT clause is used to restrict the number of rows returned by the query.
Syntax:-
SELECT column1, column2
FROM table_name
LIMIT number;
Example:
SELECT * FROM students
LIMIT 5;
→ Shows only the first 5 rows from the table.
- DISTINCT:- The DISTINCT clause ensures that duplicate values are removed from the result.
Syntax:-
SELECT DISTINCT column_name
FROM table_name;
Example:
SELECT DISTINCT grade FROM students;
→ Lists each unique grade only once.
- BETWEEN:- The BETWEEN clause is used to filter the result set within a specific range (inclusive of boundaries).
Syntax:-
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.
- IN:- The IN clause checks whether a value exists in a set of values.
Syntax:-
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’.
- LIKE:- The LIKE clause is used to search for a pattern in a column using wildcards (%, _).
Syntax:-
SELECT column1, column2
FROM table_name
WHERE column_name IN (value1, value2, …);
Example:
SELECT * FROM students
WHERE name LIKE ‘J%’;
→ Finds students whose names start with ‘J’.
- IS NULL / IS NOT NULL:- Used to test if a column contains NULL (missing value) or not.
Syntrax:-
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.