A Prepared Statement is a programming technique, especially used when working with databases, to make SQL queries more secure and efficient.
It consists of two steps:
Prepare the SQL query with placeholders
Example in SQL:
SELECT * FROM users WHERE username = ? AND password = ?
(Some languages use :username or other types of placeholders.)
Bind parameters and execute
The real values are bound later, for example:
$stmt->bind_param("ss", $username, $password);
$stmt->execute();✅ Protection against SQL injection:
User input is treated separately and safely, not directly inserted into the SQL string.
✅ Faster with repeated use:
The SQL query is parsed once by the database server and can be executed multiple times efficiently (e.g., in loops).
$conn = new mysqli("localhost", "user", "pass", "database");
$stmt = $conn->prepare("SELECT * FROM users WHERE email = ?");
$stmt->bind_param("s", $email); // "s" stands for string
$email = "example@example.com";
$stmt->execute();
$result = $stmt->get_result();A Prepared Statement separates SQL logic from user input, making it a secure (SQL Injection) and recommended practice when dealing with databases.
An Outer Join is a type of database join (commonly used in SQL) that returns records from one or both tables even if there’s no matching record in the other table.
LEFT OUTER JOIN (or simply: LEFT JOIN):
→ Returns all records from the left table, and the matching ones from the right table.
→ If there’s no match, the result is filled with NULL values from the right table.
RIGHT OUTER JOIN (or: RIGHT JOIN):
→ Returns all records from the right table, and the matching ones from the left table.
→ If there’s no match, NULL is used for the left side.
FULL OUTER JOIN:
→ Returns all records from both tables, with NULL where no match exists on either side.
Suppose you have two tables:
Customers
| CustomerID | Name |
| 1 | Anna |
| 2 | Bernd |
| 3 | Clara |
Orders
| OrderID | CustomerID | Product |
| 101 | 2 | Book |
| 102 | 4 | Lamp |
| CustomerID | Name | OrderID | Product |
|---|---|---|---|
| 1 | Anna | NULL | NULL |
| 2 | Bernd | 101 | Book |
| 3 | Clara | NULL | NULL |
Transaction Control Language (TCL) is a subset of SQL used to manage transactions in a database. A transaction is a logical unit of work that may consist of one or more SQL statements—typically INSERT, UPDATE, or DELETE—that should be executed together.
TCL provides commands to ensure that transactions are properly completed or rolled back in case of errors.
| Command | Description |
|---|---|
COMMIT |
Saves all changes made in the current transaction permanently to the database. |
ROLLBACK |
Undoes all changes made since the last COMMIT. |
SAVEPOINT |
Creates a named point within a transaction that you can roll back to later. |
ROLLBACK TO SAVEPOINT |
Reverts changes back to a specific savepoint. |
SET TRANSACTION |
Defines characteristics for the current transaction (e.g., isolation level). |
BEGIN;
UPDATE account SET balance = balance - 100 WHERE account_id = 1;
UPDATE account SET balance = balance + 100 WHERE account_id = 2;
COMMIT;→ Both updates are completed together. If an error occurs, you could use ROLLBACK to cancel both operations.
TCL commands only work in database systems that support transactions (e.g., PostgreSQL, Oracle, or MySQL with InnoDB).
Data Control Language (DCL) is a subset of SQL that focuses on managing access rights and permissions within a database. DCL commands are used to control who can do what in the database.
| Command | Description |
|---|---|
GRANT |
Gives a user specific privileges (e.g., to read or modify data) |
REVOKE |
Removes previously granted privileges from a user |
GRANT SELECT, INSERT ON Customers TO User123;
REVOKE INSERT ON Customers FROM User123;SELECT – Read data
INSERT – Add new data
UPDATE – Modify existing data
DELETE – Remove data
ALL – Grant all available privileges
DCL handles security and access control in the database.
Typically used by a database administrator (DBA).
Permissions can be granted at the table, column, or database level.
DCL operations are often transaction-dependent, requiring a COMMIT to take effect.
DDL (Data Definition Language) – Defines the database structure (e.g., tables)
DML (Data Manipulation Language) – Works with the data itself (e.g., insert or update)
TCL (Transaction Control Language) – Manages transactions (COMMIT, ROLLBACK)
DCL (Data Control Language) – Manages permissions and user access
Data Manipulation Language (DML) is a subset of SQL (Structured Query Language) used to manage and manipulate data within a database. With DML, users can insert, query, update, and delete data — essentially everything you'd typically do with data stored in a database.
| Command | Purpose |
|---|---|
SELECT |
Retrieve data from a table |
INSERT |
Add new data |
UPDATE |
Modify existing data |
DELETE |
Remove data |
-- Insert
INSERT INTO customers (name, city) VALUES ('Müller', 'Berlin');
-- Query
SELECT * FROM customers WHERE city = 'Berlin';
-- Update
UPDATE customers SET city = 'Hamburg' WHERE name = 'Müller';
-- Delete
DELETE FROM customers WHERE name = 'Müller';DML deals with the data inside tables, not with the structure of the tables themselves (that's handled by Data Definition Language, DDL).
DML operations can often be rolled back (undone), especially when transactions are supported.
In short: DML is the toolset you use to keep your database dynamic and interactive by constantly adding, reading, modifying, or deleting data.
DQL stands for Data Query Language, and it's a subset of SQL (Structured Query Language). It is used specifically to query data from a database without modifying it.
Read-only: DQL is used to retrieve data, not to insert, update, or delete it.
The primary command used is:
SELECTExample:
SELECT name, birthdate FROM customers WHERE city = 'Berlin';This command retrieves the names and birthdates of all customers living in Berlin — without changing any data.
| Sub-language | Meaning | Main Purpose |
|---|---|---|
| DQL | Data Query Language | Reading data |
| DML | Data Manipulation Language | Inserting, updating, deleting data (INSERT, UPDATE, DELETE) |
| DDL | Data Definition Language | Defining database structure (CREATE, ALTER, DROP) |
| DCL | Data Control Language | Managing access rights (GRANT, REVOKE) |
| TCL | Transaction Control Language | Handling transactions (COMMIT, ROLLBACK) |
An INNER JOIN is a term used in SQL (Structured Query Language) to combine rows from two (or more) tables based on a related column between them.
You have two tables:
Table: Customers
| CustomerID | Name |
|---|---|
| 1 | Anna |
| 2 | Bernd |
| 3 | Clara |
Table: Orders
| OrderID | CustomerID | Product |
|---|---|---|
| 101 | 1 | Book |
| 102 | 2 | Laptop |
| 103 | 4 | Phone |
Now you want to know which customers have placed orders. You only want the customers who exist in both tables.
SELECT Customers.Name, Orders.Product
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;| Name | Product |
|---|---|
| Anna | Book |
| Bernd | Laptop |
Clara didn’t place any orders → not included.
The order with CustomerID 4 doesn’t match any customer → also excluded.
An INNER JOIN returns only the rows with matching values in both tables.
An explicit join is a clear and direct way to define a join in an SQL query, where the type of join (such as INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN) is explicitly stated.
SELECT *
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;This makes it clear:
Which tables are being joined (customers, orders)
What kind of join is used (INNER JOIN)
What the join condition is (ON customers.customer_id = orders.customer_id)
An implicit join is the older style, using a comma in the FROM clause, and putting the join condition in the WHERE clause:
SELECT *
FROM customers, orders
WHERE customers.customer_id = orders.customer_id;This works the same, but it's less clear and not ideal for complex queries.
More readable and structured, especially with multiple tables
Clear separation of join conditions (ON) and filter conditions (WHERE)
Recommended in modern SQL development
An implicit join is a way of joining tables in SQL without using the JOIN keyword explicitly. Instead, the join is expressed using the WHERE clause.
SELECT *
FROM customers, orders
WHERE customers.customer_id = orders.customer_id;In this example, the tables customers and orders are joined using a condition in the WHERE clause.
SELECT *
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;| Aspect | Implicit Join | Explicit Join |
|---|---|---|
| Syntax | Tables separated by commas, joined via WHERE |
Uses JOIN and ON |
| Readability | Less readable in complex queries | More structured and readable |
| Error-proneness | Higher (e.g., accidental cross joins) | Lower, as join conditions are clearer |
| ANSI-92 compliance | Not compliant | Fully compliant |
It was common in older SQL code, but explicit joins are recommended today, as they are clearer, easier to maintain, and less error-prone, especially in complex queries involving multiple tables.
A Materialized View is a special type of database object that stores the result of a SQL query physically on disk, unlike a regular view which is computed dynamically every time it’s queried.
Stored on disk: The result of the query is saved, not just the query definition.
Faster performance: Since the data is precomputed, queries against it are typically much faster.
Needs refreshing: Because the underlying data can change, a materialized view must be explicitly or automatically refreshed to stay up to date.
| Feature | View | Materialized View |
|---|---|---|
| Storage | Only the query, no data stored | Query and data are stored |
| Performance | Slower for complex queries | Faster, as results are precomputed |
| Freshness | Always up to date | Can become stale |
| Needs refresh | No | Yes (manually or automatically) |
-- Creating a materialized view in PostgreSQL
CREATE MATERIALIZED VIEW top_customers AS
SELECT customer_id, SUM(order_total) AS total_spent
FROM orders
GROUP BY customer_id;To refresh the data:
REFRESH MATERIALIZED VIEW top_customers;For complex aggregations that are queried frequently
When performance is more important than real-time accuracy
In data warehouses or reporting systems
MariaDB
