MySQL Query Advanced: 25+ Teknik Query Lanjutan untuk Developer Professional

By | September 27, 2025
0 Comment

 

MySQL Query Advanced: 25+ Teknik Query Lanjutan untuk Developer Professional

Pernah ngerasain frustasi karena MySQL query yang lambat, atau bingung bagaimana ekstrak data complex dari database? Kalau SELECT dasar sudah dikuasai, sekarang saatnya naik level ke query advanced MySQL! Teknik-teknik ini bakal bikin kamu seperti ninja database – bisa solve problem complex dengan query yang elegant dan efficient.

Artikel ini adalah panduan komprehensif untuk teknik query lanjutan MySQL yang digunakan developer professional sehari-hari. Dari window functions sampai query optimization, dari complex joins sampai advanced analytics – semua akan kita bahas dengan contoh nyata. Siap untuk transformasi skill MySQL-mu?

Fundamental yang Harus Dikuasai Sebelum Melanjutkan

Sebelum masuk ke advanced topics, pastikan kamu sudah comfortable dengan:

  • Basic SELECT, INSERT, UPDATE, DELETE
  • JOIN operations (INNER, LEFT, RIGHT)
  • GROUP BY dan aggregate functions (COUNT, SUM, AVG)
  • Indexes dan basic query optimization

1. Window Functions – Game Changer untuk Analytical Queries

Window functions memungkinkan perhitungan across rows tanpa mengelompokkan data seperti GROUP BY.

ROW_NUMBER() – Penomoran Baris

-- Beri nomor urut untuk setiap employee per department
SELECT 
    employee_id,
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept
FROM employees;

-- Hasil:
-- employee_id | name    | department | salary | rank_in_dept
-- 101         | John    | IT         | 8000   | 1
-- 102         | Jane    | IT         | 7500   | 2
-- 103         | Bob     | HR         | 6000   | 1

RANK() dan DENSE_RANK() – Ranking dengan Handle Ties

-- Bandingkan RANK vs DENSE_RANK
SELECT 
    product_id,
    product_name,
    sales,
    RANK() OVER (ORDER BY sales DESC) as rank,
    DENSE_RANK() OVER (ORDER BY sales DESC) as dense_rank
FROM products;

-- Jika ada sales yang sama:
-- product_id | product_name | sales | rank | dense_rank
-- 1          | Laptop       | 100   | 1    | 1
-- 2          | Phone        | 100   | 1    | 1  (Sama rank)
-- 3          | Tablet       | 90    | 3    | 2  (DENSE_RANK tidak skip)
-- 4          | Mouse        | 80    | 4    | 3

LEAD() dan LAG() – Akses Data Sebelum/Sesudah

-- Bandingkan sales bulan ini dengan bulan sebelumnya
SELECT 
    month,
    sales,
    LAG(sales) OVER (ORDER BY month) as previous_month_sales,
    LEAD(sales) OVER (ORDER BY month) as next_month_sales,
    sales - LAG(sales) OVER (ORDER BY month) as growth
FROM monthly_sales;

-- Hasil:
-- month    | sales | previous_month_sales | next_month_sales | growth
-- 2024-01  | 1000  | NULL                 | 1200             | NULL
-- 2024-02  | 1200  | 1000                 | 1100             | 200
-- 2024-03  | 1100  | 1200                 | NULL             | -100

2. Common Table Expressions (CTEs) – Query yang Lebih Readable

CTEs membuat complex queries lebih mudah dibaca dan di-maintain.

Basic CTE Example

-- Hitung average salary per department, lalu cari employees di atas average
WITH department_stats AS (
    SELECT 
        department,
        AVG(salary) as avg_salary
    FROM employees
    GROUP BY department
),
high_earners AS (
    SELECT 
        e.name,
        e.department,
        e.salary,
        ds.avg_salary
    FROM employees e
    JOIN department_stats ds ON e.department = ds.department
    WHERE e.salary > ds.avg_salary
)
SELECT * FROM high_earners ORDER BY department, salary DESC;

-- Lebih readable daripada subquery nested!

Recursive CTE untuk Hierarchical Data

-- Contoh organizational hierarchy
WITH RECURSIVE employee_hierarchy AS (
    -- Anchor member: CEO (tidak punya manager)
    SELECT 
        employee_id,
        name,
        title,
        manager_id,
        0 as level,
        CAST(name AS CHAR(1000)) as hierarchy_path
    FROM employees 
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- Recursive member: subordinates
    SELECT 
        e.employee_id,
        e.name,
        e.title,
        e.manager_id,
        eh.level + 1,
        CONCAT(eh.hierarchy_path, ' -> ', e.name)
    FROM employees e
    INNER JOIN employee_hierarchy eh ON e.manager_id = eh.employee_id
)
SELECT 
    employee_id,
    name,
    title,
    level,
    hierarchy_path
FROM employee_hierarchy
ORDER BY hierarchy_path;

-- Hasil akan menampilkan seluruh struktur organisasi

3. Advanced JOIN Techniques

Beyond basic INNER JOIN, MySQL punya beberapa JOIN techniques yang powerful.

SELF JOIN untuk Comparative Analysis

-- Cari employees yang bergabung di bulan yang sama
SELECT 
    e1.name as employee1,
    e2.name as employee2,
    DATE_FORMAT(e1.hire_date, '%Y-%m') as join_month
FROM employees e1
JOIN employees e2 
    ON DATE_FORMAT(e1.hire_date, '%Y-%m') = DATE_FORMAT(e2.hire_date, '%Y-%m')
    AND e1.employee_id < e2.employee_id  -- Hindari duplicate pairs
ORDER BY join_month, employee1;

-- Cari products yang sering dibeli bersama (market basket analysis)
SELECT 
    p1.product_name as product_a,
    p2.product_name as product_b,
    COUNT(*) as times_bought_together
FROM order_items oi1
JOIN order_items oi2 
    ON oi1.order_id = oi2.order_id 
    AND oi1.product_id < oi2.product_id JOIN products p1 ON oi1.product_id = p1.product_id JOIN products p2 ON oi2.product_id = p2.product_id GROUP BY p1.product_name, p2.product_name HAVING COUNT(*) > 5
ORDER BY times_bought_together DESC;

CROSS JOIN untuk Generating Data

-- Generate calendar dates untuk tahun 2024
WITH numbers AS (
    SELECT 0 as n UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION
    SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
),
dates AS (
    SELECT 
        DATE('2024-01-01') + INTERVAL (t4.n * 1000 + t3.n * 100 + t2.n * 10 + t1.n) DAY as date
    FROM numbers t1
    CROSS JOIN numbers t2
    CROSS JOIN numbers t3
    CROSS JOIN numbers t4
    WHERE DATE('2024-01-01') + INTERVAL (t4.n * 1000 + t3.n * 100 + t2.n * 10 + t1.n) DAY < '2025-01-01'
)
SELECT date FROM dates ORDER BY date;

-- Generate report untuk semua kombinasi product dan region (meski tidak ada sales)
SELECT 
    p.product_name,
    r.region_name,
    COALESCE(SUM(s.amount), 0) as total_sales
FROM products p
CROSS JOIN regions r
LEFT JOIN sales s ON p.product_id = s.product_id AND r.region_id = s.region_id
GROUP BY p.product_name, r.region_name
ORDER BY p.product_name, r.region_name;

4. Advanced Subquery Techniques

Subquery yang tepat bisa solve problem complex dengan elegant.

Correlated Subqueries

-- Cari employees dengan salary di atas average department mereka
SELECT 
    e1.name,
    e1.department,
    e1.salary
FROM employees e1
WHERE e1.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department = e1.department  -- Correlated subquery
);

-- Cari products yang belum pernah dijual bulan ini
SELECT 
    p.product_name
FROM products p
WHERE NOT EXISTS (
    SELECT 1
    FROM sales s
    WHERE s.product_id = p.product_id
    AND YEAR(s.sale_date) = YEAR(CURRENT_DATE)
    AND MONTH(s.sale_date) = MONTH(CURRENT_DATE)
);

Derived Tables (Subquery di FROM Clause)

-- Hitung running total sales per bulan
SELECT
    month,
    sales,
    SUM(sales) OVER (ORDER BY month) as running_total,
    AVG(sales) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg_3months
FROM (
    SELECT
        DATE_FORMAT(sale_date, '%Y-%m') as month,
        SUM(amount) as sales
    FROM sales
    GROUP BY DATE_FORMAT(sale_date, '%Y-%m')
) monthly_sales
ORDER BY month;

-- Complex aggregation dengan multiple levels
SELECT
    dept_stats.department,
    dept_stats.avg_salary,
    dept_stats.max_salary,
    (SELECT COUNT(*) FROM employees e2 WHERE e2.department = dept_stats.department) as employee_count
FROM (
    SELECT
        department,
        AVG(salary) as avg_salary,
        MAX(salary) as max_salary
    FROM employees
    GROUP BY department
    HAVING AVG(salary) > 5000
) dept_stats
ORDER BY dept_stats.avg_salary DESC;

5. JSON Functions – Working dengan Semi-Structured Data

MySQL 5.7+ support JSON data type dengan functions yang powerful.

JSON Extraction dan Manipulation

-- Contoh table dengan JSON column
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    attributes JSON,
    created_at TIMESTAMP
);

-- Insert data JSON
INSERT INTO products VALUES (
    1, 
    'Smartphone', 
    '{"brand": "Samsung", "specs": {"ram": "8GB", "storage": "128GB"}, "colors": ["black", "blue"]}',
    NOW()
);

-- Extract values dari JSON
SELECT
    name,
    attributes->>"$.brand" as brand,
    attributes->>"$.specs.ram" as ram,
    attributes->>"$.specs.storage" as storage,
    JSON_LENGTH(attributes->>"$.colors") as color_count
FROM products;

-- Query berdasarkan JSON content
SELECT name, attributes
FROM products
WHERE attributes->>"$.brand" = 'Samsung'
AND JSON_CONTAINS(attributes->>"$.colors", '"blue"');

-- Update JSON data
UPDATE products 
SET attributes = JSON_SET(
    attributes,
    '$.specs.ram', '12GB',
    '$.price', 799.99
)
WHERE id = 1;

6. Full-Text Search – Advanced Text Searching

Untuk implementasi search yang powerful beyond LIKE operator.

Setup Full-Text Search

-- Buat table dengan full-text index
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    author VARCHAR(100),
    created_at TIMESTAMP,
    FULLTEXT(title, content)
) ENGINE=InnoDB;

-- Insert sample data
INSERT INTO articles (title, content, author) VALUES
('MySQL Performance Tips', 'Learn how to optimize your MySQL queries...', 'John Doe'),
('Advanced SQL Techniques', 'Window functions and CTEs can solve complex problems...', 'Jane Smith');

-- Basic full-text search
SELECT 
    title,
    content,
    MATCH(title, content) AGAINST('performance optimization') as relevance_score
FROM articles
WHERE MATCH(title, content) AGAINST('performance optimization')
ORDER BY relevance_score DESC;

-- Boolean mode untuk advanced search
SELECT title, content
FROM articles
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);

-- Query expansion untuk synonym search
SELECT title, content
FROM articles
WHERE MATCH(title, content) AGAINST('database' WITH QUERY EXPANSION);

7. Query Optimization Advanced Techniques

Teknik optimisasi untuk query yang high-performance.

EXPLAIN ANALYZE untuk Query Analysis

-- Analyze query execution plan
EXPLAIN ANALYZE
SELECT 
    c.customer_name,
    COUNT(o.order_id) as order_count,
    SUM(oi.quantity * oi.unit_price) as total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_date >= '2024-01-01'
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) > 5
ORDER BY total_spent DESC;

-- Hasil EXPLAIN akan menunjukkan:
-- * Index usage
-- * Join types
-- * Rows examined
-- * Potential bottlenecks

-- Optimized version dengan proper indexing
ALTER TABLE orders ADD INDEX idx_customer_date (customer_id, order_date);
ALTER TABLE order_items ADD INDEX idx_order (order_id);

-- Gunakan covering index dimana possible
EXPLAIN ANALYZE
SELECT 
    c.customer_name,
    o.order_count,
    o.total_spent
FROM customers c
JOIN (
    SELECT 
        customer_id,
        COUNT(*) as order_count,
        SUM(total_amount) as total_spent
    FROM orders
    WHERE order_date >= '2024-01-01'
    GROUP BY customer_id
    HAVING COUNT(*) > 5
) o ON c.customer_id = o.customer_id
ORDER BY o.total_spent DESC;

Query Hints untuk Force Optimization

-- Force index usage
SELECT *
FROM orders USE INDEX (idx_customer_date)
WHERE customer_id = 123 
AND order_date BETWEEN '2024-01-01' AND '2024-12-31';

-- Force join order
SELECT STRAIGHT_JOIN
    c.customer_name,
    o.order_date,
    o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.country = 'Indonesia'
ORDER BY o.order_date DESC;

-- Optimizer hints untuk specific strategies
SELECT /*+ MAX_EXECUTION_TIME(1000) */
    customer_id,
    COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 10;

8. Advanced Data Analysis Queries

Queries untuk business intelligence dan analytics.

Cohort Analysis

-- Cohort analysis untuk user retention
WITH user_cohorts AS (
    SELECT
        user_id,
        DATE_FORMAT(MIN(created_at), '%Y-%m-01') as cohort_month
    FROM users
    GROUP BY user_id
),
monthly_activity AS (
    SELECT
        u.user_id,
        c.cohort_month,
        DATE_FORMAT(u.created_at, '%Y-%m-01') as activity_month,
        TIMESTAMPDIFF(MONTH, c.cohort_month, u.created_at) as month_number
    FROM users u
    JOIN user_cohorts c ON u.user_id = c.user_id
    WHERE u.created_at >= c.cohort_month
)
SELECT
    cohort_month,
    month_number,
    COUNT(DISTINCT user_id) as active_users,
    ROUND(100.0 * COUNT(DISTINCT user_id) / FIRST_VALUE(COUNT(DISTINCT user_id)) 
          OVER (PARTITION BY cohort_month ORDER BY month_number), 2) as retention_rate
FROM monthly_activity
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;

Running Totals dan Moving Averages

-- Financial report dengan running totals
SELECT
    date,
    revenue,
    SUM(revenue) OVER (ORDER BY date) as running_total,
    AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7days,
    LAG(revenue, 7) OVER (ORDER BY date) as revenue_7days_ago,
    ROUND(100.0 * (revenue - LAG(revenue, 7) OVER (ORDER BY date)) / 
          LAG(revenue, 7) OVER (ORDER BY date), 2) as growth_7days
FROM daily_revenue
ORDER BY date;

9. Stored Procedures untuk Complex Business Logic

Encapsulate complex operations dalam stored procedures.

Advanced Stored Procedure Example

DELIMITER //

CREATE PROCEDURE GenerateMonthlySalesReport(
    IN p_year INT,
    IN p_month INT,
    OUT p_total_sales DECIMAL(10,2),
    OUT p_top_product VARCHAR(100)
)
BEGIN
    DECLARE v_done INT DEFAULT FALSE;
    DECLARE v_product_name VARCHAR(100);
    DECLARE v_product_sales DECIMAL(10,2);
    
    -- Temporary table untuk intermediate results
    CREATE TEMPORARY TABLE temp_product_sales (
        product_name VARCHAR(100),
        total_sales DECIMAL(10,2)
    );
    
    -- Calculate total sales
    SELECT COALESCE(SUM(oi.quantity * oi.unit_price), 0)
    INTO p_total_sales
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    WHERE YEAR(o.order_date) = p_year
    AND MONTH(o.order_date) = p_month;
    
    -- Get product-wise sales
    INSERT INTO temp_product_sales
    SELECT 
        p.product_name,
        SUM(oi.quantity * oi.unit_price) as total_sales
    FROM orders o
    JOIN order_items oi ON o.order_id = oi.order_id
    JOIN products p ON oi.product_id = p.product_id
    WHERE YEAR(o.order_date) = p_year
    AND MONTH(o.order_date) = p_month
    GROUP BY p.product_name
    ORDER BY total_sales DESC;
    
    -- Get top product
    SELECT product_name INTO p_top_product
    FROM temp_product_sales
    ORDER BY total_sales DESC
    LIMIT 1;
    
    -- Cleanup
    DROP TEMPORARY TABLE temp_product_sales;
    
END//

DELIMITER ;

-- Usage
CALL GenerateMonthlySalesReport(2024, 1, @total_sales, @top_product);
SELECT @total_sales as total_sales, @top_product as top_product;

10. Security dan Performance Best Practices

Prepared Statements untuk Security

-- Prevent SQL injection dengan prepared statements
PREPARE get_user_orders FROM '
    SELECT o.order_id, o.order_date, o.total_amount
    FROM orders o
    JOIN users u ON o.user_id = u.user_id
    WHERE u.username = ? AND o.order_date BETWEEN ? AND ?
';

SET @username = 'john_doe';
SET @start_date = '2024-01-01';
SET @end_date = '2024-12-31';

EXECUTE get_user_orders USING @username, @start_date, @end_date;

DEALLOCATE PREPARE get_user_orders;

Transaction Management untuk Data Integrity

-- Complex operation dengan transaction
START TRANSACTION;

-- Deduct inventory
UPDATE products 
SET stock_quantity = stock_quantity - 1 
WHERE product_id = 123 AND stock_quantity >= 1;

-- Check if update was successful
IF ROW_COUNT() = 0 THEN
    ROLLBACK;
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient stock';
END IF;

-- Create order
INSERT INTO orders (user_id, total_amount, status) 
VALUES (456, 99.99, 'pending');

SET @order_id = LAST_INSERT_ID();

-- Add order items
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
VALUES (@order_id, 123, 1, 99.99);

COMMIT;

Performance Monitoring Queries

Query untuk Monitor Database Performance

-- Cari slow queries
SELECT 
    query,
    execution_time,
    rows_examined,
    rows_sent
FROM mysql.slow_log 
WHERE start_time > NOW() - INTERVAL 1 DAY
ORDER BY execution_time DESC
LIMIT 10;

-- Check index usage statistics
SELECT 
    TABLE_NAME,
    INDEX_NAME,
    SEQ_IN_INDEX,
    COLUMN_NAME,
    CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX;

-- Monitor table sizes dan growth
SELECT 
    TABLE_NAME,
    ROUND((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024, 2) AS size_mb,
    TABLE_ROWS,
    AUTO_INCREMENT
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_database'
ORDER BY size_mb DESC;

Kesimpulan: Mastery melalui Practice

Menguasai MySQL query advanced techniques membutuhkan practice dan pengalaman nyata. Mulailah dengan:

  • Practice dengan dataset real yang complex
  • Analyze query performance dengan EXPLAIN
  • Experiment dengan different approaches untuk problem yang sama
  • Pelajari dari query patterns yang digunakan di production

Dengan menguasai teknik-teknik ini, kamu akan mampu menyelesaikan problem data yang complex dengan solutions yang efficient dan maintainable. Happy querying!