JOIN Queries (Laravel Query Builder)

🔹 1️⃣ INNER JOIN

👉 Sirf wahi rows jahan dono tables me match ho

$rows = DB::table('users')

    ->join('post_tasks', 'post_tasks.UserID', '=', 'users.id')

    ->select('fname', 'lname', 'TaskTitle', 'TaskID', 'UserID')

    ->groupBy('users.id')

    ->limit(10)

    ->get();

🔹 2️⃣ LEFT JOIN (LEFT OUTER JOIN)

👉 Users sab aayenge, task ho ya na ho

$rows = DB::table('users')

    ->leftJoin('post_tasks', 'post_tasks.UserID', '=', 'users.id')

    ->select('fname', 'lname', 'TaskTitle', 'TaskID', 'UserID')

    ->groupBy('users.id')

    ->limit(10)

    ->get();

🔹 3️⃣ RIGHT JOIN (RIGHT OUTER JOIN)

👉 Tasks sab aayenge, user ho ya na ho

$rows = DB::table('users')

    ->rightJoin('post_tasks', 'post_tasks.UserID', '=', 'users.id')

    ->select('fname', 'lname', 'TaskTitle', 'TaskID', 'UserID')

    ->groupBy('users.id')

    ->limit(10)

    ->get();

🔹 4️⃣ CROSS JOIN

👉 Dono tables ke saare possible combinations (Cartesian product)

$rows = DB::table('users')

    ->crossJoin('post_tasks')

    ->select('fname', 'lname', 'TaskTitle', 'TaskID', 'UserID')

    ->limit(10)

    ->get();

⚠️ Warning: Large tables me CROSS JOIN dangerous hota hai

🔹 5️⃣ FULL OUTER JOIN (MySQL workaround using UNION)

👉 Dono tables ka complete data, match ho ya na ho

$left = DB::table('users')

    ->leftJoin('post_tasks', 'post_tasks.UserID', '=', 'users.id')

    ->select('fname', 'lname', 'TaskTitle', 'TaskID', 'UserID');



$rows = DB::table('users')

    ->rightJoin('post_tasks', 'post_tasks.UserID', '=', 'users.id')

    ->select('fname', 'lname', 'TaskTitle', 'TaskID', 'UserID')

    ->union($left)

    ->limit(10)

    ->get();