Skip to main content
2026-07-29 16:01
Database

Left Join for All Users

QueryValidateMedium
Try Interactive Demo

What you will learn

  • LEFT JOIN keeps rows from the left table even when no match on right — verify expected NULL columns explicitly
  • Coalesce NULLs in downstream logic (COALESCE(right.value, default)) — SUM(NULL) returns NULL, not 0
  • Test with zero-match AND multi-match rows — LEFT JOIN with duplicates inflates row count silently

Description

List all users and their orders (including users without orders).

Test Scenario

-- Your query:
SELECT u.name, o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- Expected: All 5 users, some with NULL order data

Testing Tips

  • •LEFT JOIN keeps all left table rows
  • •NULL for non-matching right table
  • •Useful for finding "missing" relationships
// Playwright API Testing - Database
import { test, expect } from '@playwright/test';


test('Database query via API', async ({ request }) => {
  const response = await request.post('/api/database/query', {
    data: {
      sql: 'SELECT * FROM users WHERE id = ?',
      params: [1]
    }
  });


  expect(response.ok()).toBeTruthy();
  const { rows } = await response.json();
  expect(rows.length).toBeGreaterThan(0);
});

Challenge ID: sql-left-join

2 frameworks available