SQL SELECT & WHERE: Der ultimative Guide 2025

1. SELECT Grundlagen

Das SELECT Statement ist der Ausgangspunkt jeder Datenabfrage. Es bestimmt, welche Spalten aus einer Tabelle abgerufen werden sollen.

🎯 Einfache SELECT-Abfragen

-- Alle Spalten auswählen
SELECT * FROM users;

-- Spezifische Spalten auswählen
SELECT name, email, created_at 
FROM users;

-- Spalten-Aliase verwenden
SELECT 
    name AS 'Benutzername',
    email AS 'E-Mail-Adresse',
    created_at AS 'Registriert am'
FROM users;

🧮 Berechnete Spalten

-- Mathematische Operationen
SELECT 
    product_name,
    price,
    price * 1.19 AS 'price_with_tax',
    quantity,
    price * quantity AS 'total_value'
FROM products;

-- String-Funktionen
SELECT 
    CONCAT(first_name, ' ', last_name) AS 'full_name',
    UPPER(email) AS 'email_upper',
    LENGTH(description) AS 'description_length'
FROM users;

2. WHERE Bedingungen

Die WHERE Klausel filtert Zeilen basierend auf bestimmten Bedingungen. Sie ist essentiell für präzise Datenabfragen.

⚖️ Vergleichsoperatoren

-- Gleichheit und Ungleichheit
SELECT * FROM users WHERE status = 'active';
SELECT * FROM users WHERE status != 'inactive';
SELECT * FROM users WHERE status <> 'banned';

-- Numerische Vergleiche
SELECT * FROM products WHERE price > 100;
SELECT * FROM products WHERE price >= 50 AND price <= 200;
SELECT * FROM orders WHERE quantity < 10;

🔍 Pattern Matching mit LIKE

-- Wildcards verwenden
SELECT * FROM users WHERE name LIKE 'Max%';        -- Beginnt mit "Max"
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- Endet mit "@gmail.com"
SELECT * FROM products WHERE name LIKE '%laptop%';   -- Enthält "laptop"

-- Einzelne Zeichen mit Unterstrich
SELECT * FROM products WHERE sku LIKE 'ABC_123';     -- ABC + ein Zeichen + 123

-- Case-insensitive Suche (MySQL)
SELECT * FROM users WHERE LOWER(name) LIKE LOWER('%MARTIN%');

📋 IN, BETWEEN und NULL

-- IN Operator für mehrere Werte
SELECT * FROM users 
WHERE status IN ('active', 'pending', 'verified');

-- BETWEEN für Bereiche
SELECT * FROM orders 
WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

SELECT * FROM products 
WHERE price BETWEEN 50 AND 200;

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

🔗 Logische Operatoren

-- AND, OR, NOT kombinieren
SELECT * FROM users 
WHERE status = 'active' 
  AND created_at >= '2024-01-01'
  AND email LIKE '%@company.com';

SELECT * FROM products 
WHERE (category = 'electronics' OR category = 'computers')
  AND price < 1000
  AND in_stock = TRUE;

-- NOT Operator
SELECT * FROM users 
WHERE NOT status = 'banned';

3. ORDER BY & LIMIT

ORDER BY sortiert die Ergebnisse, während LIMIT die Anzahl der zurückgegebenen Zeilen begrenzt.

📊 Sortierung mit ORDER BY

-- Aufsteigende Sortierung (Standard)
SELECT * FROM users ORDER BY name;
SELECT * FROM users ORDER BY name ASC;

-- Absteigende Sortierung
SELECT * FROM products ORDER BY price DESC;

-- Mehrere Spalten sortieren
SELECT * FROM users 
ORDER BY status ASC, created_at DESC, name ASC;

-- Nach berechneten Spalten sortieren
SELECT 
    name, 
    price, 
    quantity,
    (price * quantity) AS total_value
FROM products 
ORDER BY total_value DESC;

🔢 Limitierung mit LIMIT

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

-- Paginierung mit OFFSET (MySQL/PostgreSQL)
SELECT * FROM users 
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;  -- Seite 3 (20 pro Seite)

-- Alternative Syntax für MySQL
SELECT * FROM users 
ORDER BY created_at DESC
LIMIT 40, 20;  -- OFFSET, COUNT

4. Aggregatfunktionen

Aggregatfunktionen führen Berechnungen über mehrere Zeilen durch und geben einen einzelnen Wert zurück. Sie sind essentiell für statistische Auswertungen und Berichte.

📈 Grundlegende Aggregatfunktionen

-- COUNT: Anzahl der Zeilen
SELECT COUNT(*) AS total_users FROM users;
SELECT COUNT(email) AS users_with_email FROM users;
SELECT COUNT(DISTINCT city) AS unique_cities FROM users;

-- SUM: Summe numerischer Werte
SELECT SUM(amount) AS total_revenue FROM orders;
SELECT SUM(quantity) AS total_items_sold FROM order_items;

-- AVG: Durchschnittswert
SELECT AVG(price) AS average_price FROM products;
SELECT ROUND(AVG(rating), 2) AS avg_rating FROM reviews;

-- MIN/MAX: Minimum und Maximum
SELECT 
    MIN(price) AS cheapest_product,
    MAX(price) AS most_expensive,
    MIN(created_at) AS first_order,
    MAX(created_at) AS latest_order
FROM products;

🎯 Kombinierte Aggregationen

-- Dashboard-Statistiken
SELECT 
    COUNT(*) AS total_orders,
    SUM(amount) AS total_revenue,
    AVG(amount) AS average_order_value,
    MIN(amount) AS smallest_order,
    MAX(amount) AS largest_order
FROM orders 
WHERE status = 'completed'
  AND created_at >= '2024-01-01';

5. GROUP BY & HAVING

GROUP BY gruppiert Zeilen mit gleichen Werten, während HAVING Gruppen basierend auf Aggregatfunktionen filtert.

📊 Gruppierung mit GROUP BY

-- Benutzer nach Status gruppieren
SELECT 
    status,
    COUNT(*) AS user_count
FROM users 
GROUP BY status
ORDER BY user_count DESC;

-- Umsatz nach Monat
SELECT 
    DATE_FORMAT(created_at, '%Y-%m') AS month,
    COUNT(*) AS order_count,
    SUM(amount) AS monthly_revenue
FROM orders 
WHERE status = 'completed'
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month DESC;

-- Mehrere Spalten gruppieren
SELECT 
    category,
    brand,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products 
GROUP BY category, brand
ORDER BY category, avg_price DESC;

🔍 Filterung mit HAVING

-- Nur Kategorien mit mehr als 10 Produkten
SELECT 
    category,
    COUNT(*) AS product_count,
    AVG(price) AS avg_price
FROM products 
GROUP BY category
HAVING COUNT(*) > 10
ORDER BY product_count DESC;

-- Kunden mit hohem Bestellwert
SELECT 
    customer_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_spent,
    AVG(amount) AS avg_order_value
FROM orders 
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(amount) > 1000 
   AND COUNT(*) >= 5
ORDER BY total_spent DESC;

6. Performance-Tipps

📊 Optimierte vs. Unoptimierte Abfragen

-- ❌ Schlecht: SELECT * und keine Indexe
SELECT * FROM users 
WHERE UPPER(email) LIKE UPPER('%@GMAIL.COM');

-- ✅ Besser: Spezifische Spalten und Index-freundlich
SELECT id, name, email, created_at 
FROM users 
WHERE email LIKE '%@gmail.com'
ORDER BY created_at DESC
LIMIT 100;

-- ❌ Schlecht: Funktionen in WHERE
SELECT * FROM orders 
WHERE YEAR(created_at) = 2024;

-- ✅ Besser: Index-freundliche Bereiche
SELECT * FROM orders 
WHERE created_at >= '2024-01-01' 
  AND created_at < '2025-01-01';

🔍 Laravel Eloquent vs. Raw SQL

// Laravel Eloquent - gut für einfache Abfragen
User::where('status', 'active')
    ->orderBy('created_at', 'desc')
    ->limit(10)
    ->get();

// Raw SQL - besser für komplexe Aggregationen
DB::select('
    SELECT 
        category,
        COUNT(*) as product_count,
        AVG(price) as avg_price
    FROM products 
    WHERE created_at >= ? 
    GROUP BY category 
    HAVING COUNT(*) > 5
    ORDER BY avg_price DESC
', ['2024-01-01']);