Common patterns and operations for everyday database tasks.
JOIN operations across different join types.
Topics covered:
- INNER JOIN - matching records only
- LEFT JOIN - all records from left table
- RIGHT JOIN - all records from right table
- Multiple joins in a single query
- Join conditions with complex expressions
- Selecting columns from joined tables
- Alias usage in joins
- LATERAL JOIN - correlated subqueries (PostgreSQL/MySQL only)
GROUP BY, HAVING, and aggregate functions.
Topics covered:
- COUNT, SUM, AVG, MIN, MAX functions
- GROUP BY for grouping results
- HAVING for filtering grouped results
- Multiple aggregate functions in one query
- Aggregations with JOINs
- Conditional aggregations
- DISTINCT counts
LIMIT and OFFSET for pagination.
Topics covered:
- Basic LIMIT for result limiting
- OFFSET for skipping records
- Calculating pagination parameters
- Page-based navigation
- Total count queries
- Efficient pagination patterns
Note: This example covers manual pagination. For advanced pagination features (full pagination, simple pagination, cursor pagination), see 03-pagination-advanced.php.
Transaction management for data consistency.
Topics covered:
- Starting transactions with
startTransaction() - Committing transactions with
commit() - Rolling back with
rollback() - Nested operations in transactions
- Error handling in transactions
- Transaction isolation levels
Savepoints and nested transactions.
Topics covered:
- Creating savepoints within transactions
- Rolling back to specific savepoints
- Releasing savepoints without rolling back
- Nested savepoint management
- Error handling with savepoints
- Savepoint stack tracking
INSERT ... SELECT operations for copying data between tables.
Topics covered:
- INSERT ... SELECT with QueryBuilder
- INSERT ... SELECT with subqueries
- INSERT ... SELECT with CTEs
- Column mapping
- ON DUPLICATE KEY UPDATE (MySQL/MariaDB)
- ON CONFLICT (PostgreSQL)
UPDATE and DELETE operations with JOIN clauses.
Topics covered:
- UPDATE with JOIN (MySQL/MariaDB/PostgreSQL)
- DELETE with JOIN (MySQL/MariaDB/PostgreSQL)
- UPDATE with LEFT JOIN
- Multiple JOINs in UPDATE/DELETE
- Dialect-specific syntax handling
php 01-joins.phpPDODB_DRIVER=mysql php 01-joins.phpPDODB_DRIVER=pgsql php 01-joins.php- Joins - JOIN operations
- Aggregations - GROUP BY, HAVING
- Ordering & Pagination - ORDER BY, LIMIT, OFFSET
- Transactions - Transaction management
- Pagination - Advanced pagination
After mastering these intermediate concepts, explore:
- Advanced Examples - Connection pooling, upserts, subqueries, MERGE statements, window functions, CTEs
- Helper Functions - SQL helper functions
- Real-World Examples - Complete application patterns