Java优学网MySQL左连接入门解析：快速掌握多表查询技巧，轻松解决数据关联难题

SELECT 列名 FROM 左表 LEFT JOIN 右表 ON 连接条件;

CREATE INDEX idx_orders_user_id ON orders(user_id);

SELECT u.username, COALESCE(o.order_amount, 0) as amount FROM users u LEFT JOIN orders o ON u.id = o.user_id;

SELECT

u.username,
p.product_name,
COALESCE(COUNT(DISTINCT v.id), 0) as view_count,
COALESCE(COUNT(DISTINCT f.id), 0) as favorite_count,
CASE 
    WHEN o.id IS NOT NULL THEN '已购买'
    ELSE '未购买'
END as purchase_status

FROM users u LEFT JOIN user_views v ON u.id = v.user_id AND v.view_date >= DATE_SUB(NOW(), INTERVAL 30 DAY) LEFT JOIN user_favorites f ON u.id = f.user_id LEFT JOIN orders o ON u.id = o.user_id AND o.product_id = p.id LEFT JOIN products p ON v.product_id = p.id WHERE u.id = 123 GROUP BY u.id, p.id;