Core Concepts of DQL

Data Query Language (DQL) primarily utilizes the SELECT statement for retrieving information from databases. This functionality forms the backbone of database operations, enabling both simple single-table queries and complex multi-table joins with nested conditions.

SELECT Statement Structure

SELECT [ALL | DISTINCT]
{ * | table.* | [table.column1 [AS alias1] [, table.column2 [AS alias2]] [, ...]] }
FROM table_name [AS table_alias]
[LEFT|RIGHT|INNER JOIN table_name2]
[WHERE ...]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...]
[LIMIT {[offset,] row_count | row_count OFFSET offset}];

Square brackets indicate optional components, while curly braces denote required elements.

Column Selecsion Techniques

The initial portion of the SELECT statement determines which data columns to retrieve:

• ALL (default): Returns all matching records including duplicates
• DISTINCT: Eliminates duplicate values from results
• *: Selects every column from the specified table
• table.*: Retrieves all columns from a specific table
• Individual columns can be aliased using the AS keyword

Table aliases simplify references throughout the query:

SELECT s.student_id AS id, s.full_name AS name
FROM students AS s;

Filtering with WHERE Clause

The WHERE clause filters records based on specified conditions using logical and comparison operators.

Logical Operators

Comparison Operators

Example implementation:

SELECT product_name, price
FROM inventory
WHERE category = 'electronics' AND price > 100;

NULL Value Handling

NULL represents unknown or missing data, distinct from empty strings or zero values. Special operators handle NULL comparisons:

SELECT * FROM employees WHERE department IS NULL;
SELECT * FROM employees WHERE manager_id IS NOT NULL;

Range Queries

BETWEEN operator defines inclusive ranges:

SELECT * FROM courses 
WHERE duration BETWEEN 110 AND 120;

-- Equivalent to:
SELECT * FROM courses 
WHERE duration >= 110 AND duration <= 120;

Pattern Matching

LIKE operator enables wildcard searches:

• % matches zero or more characters
• _ matches exactly one character

SELECT * FROM courses 
WHERE course_title LIKE '%Mathematics%';

SELECT student_id, name FROM students 
WHERE name LIKE 'Smith_';

Set-Based Conditions

IN operator simplifies multiple equality checks:

SELECT * FROM products 
WHERE category IN ('books', 'music', 'games');

-- Alternative using OR:
SELECT * FROM products 
WHERE category = 'books' OR category = 'music' OR category = 'games';

Multi-Table Operations

JOIN Operations

JOIN clauses combine rows from multiple tables based on related columns.

INNER JOIN: Returns only matching records from both tables

SELECT s.name, c.course_title
FROM students s
INNER JOIN enrollments e ON s.id = e.student_id
INNER JOIN courses c ON e.course_id = c.id;

LEFT JOIN: Returns all records from the left table and matching records from the right

SELECT s.name, c.course_title
FROM students s
LEFT JOIN enrollments e ON s.id = e.student_id
LEFT JOIN courses c ON e.course_id = c.id;

RIGHT JOIN: Returns all records from the right table and matching records from the left

MySQL syntax accommodates various JOIN configurations to support flexible data retrieval patterns.