LEFT JOIN in SQL Queries

In SQL, the LEFT JOIN operation is a powerful tool for combining data from multiple tables. Let’s explore a SQL query that utilizes LEFT JOIN and dissect its components to understand its functionality better.

SELECT
  workEffort.id as ID,
  workEffort.name as Name,

  statusId.id as Status_Record_Id,
  statusId.custrecord_grw007_wrkeffstat_type as Status_ID,
  statusName.name as Status_Name,

  priorityId.id as Priority_Record_Id,
  priorityId.custrecord_grw007_wrkeffprio_priotype as Priority_ID,
  priorityName.name as Priority_Name
FROM
  customrecord_grw007_workeffort workEffort
LEFT JOIN
  customrecord_grw007_wrkeffstatus statusId ON workEffort.id = statusId.custrecord_grw007_wrkeffstat_wrkeffortid
LEFT JOIN
  customrecord_grw007_wrkeffstatustype statusName ON statusId.custrecord_grw007_wrkeffstat_type = statusName.id
LEFT JOIN
  customrecord_grw007_wrkeffprio priorityId ON workEffort.id = priorityId.custrecord_grw007_wrkeffprio_wrkeff
LEFT JOIN
  customrecord_grw007_wrkeffpriotype priorityName ON priorityId.custrecord_grw007_wrkeffprio_priotype = priorityName.id
WHERE
  workEffort.id IN (1, 118, 500)
ORDER BY
  workEffort.id;

Explanation:

1. SELECT Clause:

  – The `SELECT` clause specifies the columns to be retrieved in the result set.

  – `workEffort.id` and `workEffort.name` are selected from the `customrecord_grw007_workeffort` table to fetch the ID and Name of work efforts.

2. FROM Clause:

  – The `FROM` clause identifies the primary table from which data is selected.

  – `customrecord_grw007_workeffort` is the primary table from which work efforts are selected.

3. LEFT JOINs:

  – Multiple LEFT JOINs are used to combine data from related tables.

  – For example, `LEFT JOIN customrecord_grw007_wrkeffstatus statusId ON workEffort.id = statusId.custrecord_grw007_wrkeffstat_wrkeffortid` connects the `workEffort` table with the `customrecord_grw007_wrkeffstatus` table based on the common ID.

  – Similarly, other LEFT JOINs connect the primary table with tables holding status and priority information.

4. WHERE Clause:

  – The `WHERE` clause filters the rows returned by the query.

  – `workEffort.id IN (1, 118, 500)` restricts the results to work efforts with IDs 1, 118, or 500.

5. ORDER BY Clause:

  – The `ORDER BY` clause sorts the result set based on the specified column(s).

  – `workEffort.id` sorts the results based on the ID of work efforts.

This SQL query fetches data about work efforts along with their associated status and priority information. LEFT JOIN ensures that even if there are work efforts without corresponding status or priority records, those work efforts will still appear in the result set.