Join Query

A JOIN query is used to combine rows from two or more tables based on a related column between them. There are several types of JOINs in MySQL, including INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

INNER JOIN

The INNER JOIN returns only the rows that have matching values in both tables.

1
2
3
4
SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

LEFT JOIN

The LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match, the result is NULL on the right side.

1
2
3
4
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

RIGHT JOIN

The RIGHT JOIN returns all rows from the right table and the matched rows from the left table. If there is no match, the result is NULL on the left side.

1
2
3
4
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

FULL OUTER JOIN

The FULL OUTER JOIN returns all rows when there is a match in either left or right table. If there is no match, the result is NULL on the side that does not have a match.

1
2
3
4
SELECT columns
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;

Note: MySQL does not support FULL OUTER JOIN directly, but you can achieve it using a combination of LEFT JOIN and RIGHT JOIN with UNION.
1
2
3
4
5
6
7
8
9
SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column
UNION
SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

UNION

The UNION operator is used to combine the result sets of two or more SELECT statements. Each SELECT statement within the UNION must have the same number of columns in the result sets with similar data types.

1
2
3
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;

SELF JOIN

A SELF JOIN is a regular join but the table is joined with itself. It is useful for querying hierarchical data or comparing rows within the same table.

1
2
3
4
SELECT a.column1, b.column2
FROM table a
INNER JOIN table a as b
ON a.common_column = b.common_column;

SELECT Query

The SELECT statement is used to query data from a database. It allows you to specify the columns you want to retrieve and the conditions for filtering the data.

Basic SELECT Query

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
SELECT column1, column2, ...
FROM table_name
JOIN another_table ON table_name.common_column = another_table.common_column
WHERE condition
GROUP BY column 
HAVING condition
ORDER BY column
LIMIT number;

SELECT 
    t.customer_id, 
    t.customer_name, 
    SUM(a.order_amount) AS total_sales
FROM customers AS t
INNER JOIN orders AS a ON t.customer_id = a.customer_id
WHERE t.status = 'active'
  AND a.order_date >= '2024-01-01'
GROUP BY t.customer_id
HAVING total_sales > 1000
ORDER BY total_sales DESC
LIMIT 10;

Subquery

A subquery is a query nested inside another query. It can be used in various places such as the SELECT, FROM, WHERE, and HAVING clauses to perform operations that require multiple steps.

1
2
3
SELECT column1, column2
FROM table_name
WHERE column3 IN (SELECT column3 FROM another_table WHERE condition);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_amount > 1000
);

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN ANY (
    SELECT customer_id
    FROM orders
    WHERE order_amount > 1000
);

SELECT customer_id, customer_name
FROM customers
WHERE customer_id IN ALL (
    SELECT customer_id
    FROM orders
    WHERE order_amount > 1000
);
1
2
3
4
5
6
7
8
9
10
11
SELECT customer_id, customer_name
FROM (
    SELECT customer_id, customer_name
    FROM customers
    WHERE status = 'active'
) AS active_customers
WHERE customer_id IN (
    SELECT customer_id
    FROM orders
    WHERE order_amount > 1000
);
1
2
3
4
5
6
-- copy table structure
CREATE TABLE new_table LIKE existing_table;
CREATE TABLE new_table AS SELECT * FROM existing_table WHERE 1=0;

-- copy data from existing_table to new_table
INSERT INTO new_table SELECT * FROM existing_table;

Exists

The EXISTS operator is used to test for the existence of any record in a subquery. It returns TRUE if the subquery returns one or more records, and FALSE if it returns no records.

1
2
3
SELECT column1, column2
FROM table_name
WHERE EXISTS (SELECT column3 FROM another_table WHERE condition);

1
2
3
4
5
6
7
8
SELECT customer_id, customer_name
FROM customers
WHERE EXISTS (
    SELECT 1
    FROM orders
    WHERE orders.customer_id = customers.customer_id
      AND order_amount > 1000
);

Common Table Expressions (CTE)

A Common Table Expression (CTE) is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs are defined using the WITH clause and can improve the readability and maintainability of complex queries.

1
2
3
4
5
6
7
8
9
WITH cte_name AS (
    SELECT column1, column2
    FROM table_name
    WHERE condition
)

SELECT column1, column2
FROM cte_name
WHERE another_condition;

1
2
3
4
5
6
7
8
9
10
11
12
13
WITH active_customers AS (
    SELECT customer_id, customer_name
    FROM customers
    WHERE status = 'active'
), high_value_orders AS (
    SELECT customer_id, SUM(order_amount) AS total_sales
    FROM orders
    GROUP BY customer_id
    HAVING total_sales > 1000
)
SELECT ac.customer_id, ac.customer_name, hvo.total_sales
FROM active_customers AS ac
JOIN high_value_orders AS hvo ON ac.customer_id = hvo.customer_id;

Query Cache

MySQL’s query cache is a feature that stores the results of SELECT statements and reuses them when the same query is executed again. This can significantly improve the performance of queries that are executed frequently.

Query Cache Configuration

To enable the query cache, you need to set the query_cache_type parameter to 1 in the my.cnf file. This will enable the query cache for all databases.

1
2
3
[mysqld]
query_cache_type=1 # Enable the query cache for all databases by default
query_cache_type=DEMAND # Enable the query cache only for queries that explicitly request it

To enable the query cache for a specific database, you can use the query_cache_type parameter in the CREATE DATABASE statement.

1
SELECT SQL_CACHE * FROM table_name WHERE condition; -- Enable query cache for this specific query only works before MySQL 8.0.13