Salin dan Bagikan

Cara Menggunakan SELECT Statement di MySQL: Query Data Lengkap dengan Filtering

SELECT statement adalah perintah SQL yang paling sering digunakan. Setelah mengajari SQL ke ratusan developers, saya tahu bahwa mastery SELECT statement adalah fondasi dari database proficiency. Artikel ini membahas semua aspek SELECT dari basic hingga advanced.

SELECT Statement Basic

1. Select All Columns

-- Select semua kolom (HINDARI di production!)
SELECT * FROM users;

-- Select specific columns (RECOMMENDED)
SELECT id, username, email FROM users;

2. Select dengan Alias

-- Column alias
SELECT 
    id AS user_id,
    username AS user_name,
    email AS user_email
FROM users;

-- Table alias
SELECT u.id, u.username, u.email
FROM users AS u;

3. Select Distinct Values

-- Unique values saja
SELECT DISTINCT status FROM orders;

-- Distinct combinations
SELECT DISTINCT country, city FROM customers;

Filtering dengan WHERE Clause

1. Basic Comparison Operators

-- Equal
SELECT * FROM users WHERE status = 'active';

-- Not equal
SELECT * FROM users WHERE status != 'inactive';
SELECT * FROM users WHERE status <> 'inactive';

-- Greater than
SELECT * FROM products WHERE price > 100;

-- Less than
SELECT * FROM products WHERE stock < 10;

-- Greater than or equal
SELECT * FROM orders WHERE total >= 1000;

-- Less than or equal
SELECT * FROM users WHERE age <= 25;

-- Between range
SELECT * FROM products WHERE price BETWEEN 50 AND 100;

-- IN operator
SELECT * FROM users WHERE status IN ('active', 'verified');

-- NOT IN
SELECT * FROM users WHERE status NOT IN ('banned', 'suspended');

2. Pattern Matching dengan LIKE

-- Starts with
SELECT * FROM users WHERE username LIKE 'john%';

-- Ends with
SELECT * FROM users WHERE email LIKE '%@gmail.com';

-- Contains
SELECT * FROM products WHERE name LIKE '%laptop%';

-- Single character wildcard
SELECT * FROM users WHERE username LIKE 'john_';  -- john1, john2, johna, dll

-- Escape wildcard characters
SELECT * FROM products WHERE name LIKE '50\%%' ESCAPE '\';  -- 50% discount

3. NULL Checks

-- IS NULL
SELECT * FROM users WHERE phone IS NULL;

-- IS NOT NULL
SELECT * FROM users WHERE phone IS NOT NULL;

-- COALESCE untuk handle NULL
SELECT 
    username,
    COALESCE(phone, 'No phone') AS phone_display
FROM users;

4. Multiple Conditions dengan AND/OR

-- AND (semua kondisi harus true)
SELECT * FROM products 
WHERE category = 'electronics' 
  AND price > 500 
  AND stock > 0;

-- OR (salah satu kondisi true)
SELECT * FROM orders 
WHERE status = 'cancelled' 
   OR status = 'refunded';

-- Kombinasi AND dan OR (gunakan parentheses)
SELECT * FROM products 
WHERE (category = 'electronics' OR category = 'computers')
  AND price > 100
  AND stock > 0;

Sorting dengan ORDER BY

1. Basic Sorting

-- Ascending (default)
SELECT * FROM products ORDER BY price;
SELECT * FROM products ORDER BY price ASC;

-- Descending
SELECT * FROM products ORDER BY price DESC;

-- Multiple columns
SELECT * FROM products 
ORDER BY category ASC, price DESC;

2. Sorting dengan Expressions

-- Sort dengan calculated column
SELECT 
    name,
    price,
    discount,
    (price * (1 - discount/100)) AS final_price
FROM products
ORDER BY final_price DESC;

-- Sort dengan FIELD (custom order)
SELECT * FROM orders 
ORDER BY FIELD(status, 'pending', 'processing', 'shipped', 'delivered');

Limiting Results dengan LIMIT

1. Basic Limit

-- Top 10
SELECT * FROM products ORDER BY sales DESC LIMIT 10;

-- Skip 20, ambil 10 (pagination page 3)
SELECT * FROM products LIMIT 20, 10;

-- Atau dengan OFFSET syntax (MySQL 4.0+)
SELECT * FROM products LIMIT 10 OFFSET 20;

2. Pagination Formula

-- Page 1: LIMIT 0, 10
-- Page 2: LIMIT 10, 10
-- Page N: LIMIT (N-1)*page_size, page_size

-- Contoh: Page 5 dengan page size 20
SELECT * FROM products LIMIT 80, 20;  -- (5-1)*20 = 80

Joining Tables

1. INNER JOIN

-- Join users dan orders
SELECT 
    u.username,
    u.email,
    o.order_id,
    o.total_amount,
    o.status
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- Join dengan multiple tables
SELECT 
    u.username,
    o.order_id,
    p.name AS product_name,
    oi.quantity
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id;

2. LEFT JOIN

-- Semua users, bahkan yang belum pernah order
SELECT 
    u.username,
    u.email,
    COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.username, u.email;

3. RIGHT JOIN

-- Semua orders dengan user info (jika ada)
SELECT 
    o.order_id,
    o.total_amount,
    u.username
FROM orders o
RIGHT JOIN users u ON o.user_id = u.id;

Aggregation Functions

1. Basic Aggregations

-- COUNT
SELECT COUNT(*) AS total_users FROM users;
SELECT COUNT(DISTINCT country) AS unique_countries FROM users;

-- SUM
SELECT SUM(total_amount) AS total_revenue FROM orders;

-- AVG
SELECT AVG(price) AS avg_price FROM products;

-- MIN dan MAX
SELECT 
    MIN(price) AS cheapest,
    MAX(price) AS most_expensive
FROM products;

2. GROUP BY

-- Group by single column
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products
GROUP BY category;

-- Group by multiple columns
SELECT 
    category,
    status,
    COUNT(*) AS count,
    SUM(price) AS total_value
FROM products
GROUP BY category, status;

3. HAVING Clause

-- Filter setelah grouping
SELECT 
    category,
    COUNT(*) AS product_count
FROM products
GROUP BY category
HAVING COUNT(*) > 5;

-- Having dengan aggregation
SELECT 
    user_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS total_spent
FROM orders
GROUP BY user_id
HAVING total_spent > 1000;

Subqueries

1. Subquery dalam WHERE

-- Users yang pernah order
SELECT * FROM users 
WHERE id IN (SELECT DISTINCT user_id FROM orders);

-- Products dengan price di atas average
SELECT * FROM products 
WHERE price > (SELECT AVG(price) FROM products);

2. Subquery dalam FROM

-- Derived table
SELECT category_stats.category, category_stats.avg_price
FROM (
    SELECT 
        category,
        AVG(price) AS avg_price
    FROM products
    GROUP BY category
) AS category_stats
WHERE category_stats.avg_price > 100;

3. Correlated Subquery

-- Products dengan price di atas average category-nya
SELECT p1.*
FROM products p1
WHERE p1.price > (
    SELECT AVG(p2.price)
    FROM products p2
    WHERE p2.category = p1.category
);

UNION dan UNION ALL

1. Combine Results

-- Combine dua queries (remove duplicates)
SELECT username, email FROM users WHERE status = 'active'
UNION
SELECT username, email FROM archived_users;

-- Keep duplicates
SELECT username, email FROM users WHERE status = 'active'
UNION ALL
SELECT username, email FROM archived_users;

-- Union dengan ORDER BY
SELECT username, 'active' AS source FROM users WHERE status = 'active'
UNION ALL
SELECT username, 'archived' FROM archived_users
ORDER BY username;

Advanced SELECT Techniques

1. CASE Statements

-- Conditional dalam SELECT
SELECT 
    name,
    price,
    CASE 
        WHEN price < 50 THEN 'Budget'
        WHEN price < 200 THEN 'Mid-range'
        ELSE 'Premium'
    END AS price_category
FROM products;

-- CASE dengan aggregation
SELECT 
    CASE 
        WHEN total_amount < 100 THEN 'Small'
        WHEN total_amount < 500 THEN 'Medium'
        ELSE 'Large'
    END AS order_size,
    COUNT(*) AS order_count
FROM orders
GROUP BY order_size;

2. IF dan IFNULL

-- IF function
SELECT 
    name,
    IF(stock > 0, 'In Stock', 'Out of Stock') AS availability
FROM products;

-- IFNULL untuk handle NULL
SELECT 
    username,
    IFNULL(phone, 'No phone number') AS phone
FROM users;

-- COALESCE untuk multiple alternatives
SELECT 
    username,
    COALESCE(mobile, phone, 'No contact') AS contact
FROM users;

3. String Functions

-- CONCAT
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

-- SUBSTRING
SELECT SUBSTRING(phone, 1, 4) AS area_code FROM users;

-- LENGTH
SELECT name, LENGTH(description) AS desc_length FROM products;

-- UPPER/LOWER
SELECT UPPER(username) FROM users;

Best Practices SELECT

1. Performance Tips

-- Select specific columns
SELECT id, name, price FROM products;

-- Gunakan indexes (cek dengan EXPLAIN)
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Limit results jika tidak perlu semua
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

2. Readability

-- Format dengan baik
SELECT 
    u.username,
    p.name AS product_name,
    oi.quantity,
    (oi.quantity * oi.unit_price) AS item_total
FROM users u
INNER JOIN orders o ON u.id = o.user_id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.status = 'completed'
ORDER BY o.created_at DESC
LIMIT 10;

Kesimpulan

SELECT statement mastery meliputi:

Filtering dengan WHERE dan operators
Sorting dengan ORDER BY
Limiting dengan LIMIT
Joining tables
Aggregating data
Subqueries untuk complex logic

Dengan menguasai SELECT, Anda bisa extract insights dari data dengan efisien.

Cara Menggunakan SELECT Statement di MySQL: Query Data Lengkap dengan Filtering

Cara Menggunakan SELECT Statement di MySQL: Query Data Lengkap dengan Filtering

SELECT Statement Basic

1. Select All Columns

2. Select dengan Alias

3. Select Distinct Values

Filtering dengan WHERE Clause

1. Basic Comparison Operators

2. Pattern Matching dengan LIKE

3. NULL Checks

4. Multiple Conditions dengan AND/OR

Sorting dengan ORDER BY

1. Basic Sorting

2. Sorting dengan Expressions

Limiting Results dengan LIMIT

1. Basic Limit

2. Pagination Formula

Joining Tables

1. INNER JOIN

2. LEFT JOIN

3. RIGHT JOIN

Aggregation Functions

1. Basic Aggregations

2. GROUP BY

3. HAVING Clause

Subqueries

1. Subquery dalam WHERE

2. Subquery dalam FROM

3. Correlated Subquery

UNION dan UNION ALL

1. Combine Results

Advanced SELECT Techniques

1. CASE Statements

2. IF dan IFNULL

3. String Functions

Best Practices SELECT

1. Performance Tips

2. Readability

Kesimpulan

Artikel Terkait