Sql Left (Outer) Join

The SQL LEFT JOIN keyword returns all records from the left table (tableA), with the matching records in the right table (tableB).

The query result is NULL in the right side table when there is no match records.

The LEFT JOIN keyword in SQL delivers all matching records (or rows), as well as records (or rows) that are available in the left table but not in the right table, as well as matched values from the right table or NULL if no row in the second table matches.

This implies that if the ON clause matches 0 (zero) entries in the right table, the join will still return a row in the output, but NULL in each of the right table's columns.

Syntax:

SELECT column_name(s) 
FROM tableA 
LEFT JOIN tableB ON tableA.column_name = tableB.column_name;

Here, the given condition could be any given expression based on your requirement.

Example: Consider the following two tables,

Table 1 − CUSTOMERS Table is as follows.

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

Table 2 − Orders Table is as follows.

+-----+---------------------+-------------+--------+
| OID | DATE                | CUSTOMER_ID | AMOUNT |
+-----+---------------------+-------------+--------+
| 102 | 2009-10-08 00:00:00 |           3 |   3000 |
| 100 | 2009-10-08 00:00:00 |           3 |   1500 |
| 101 | 2009-11-20 00:00:00 |           2 |   1560 |
| 103 | 2008-05-20 00:00:00 |           4 |   2060 |
+-----+---------------------+-------------+--------+

Now, let us join these two tables using the LEFT JOIN as follows.

SELECT  ID, NAME, AMOUNT, DATE
  FROM CUSTOMERS
  LEFT JOIN ORDERS
  ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output :

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                |
+----+----------+--------+---------------------+
|  1 | Ramesh   |   NULL | NULL                |
|  2 | Khilan   |   1560 | 2009-11-20 00:00:00 |
|  3 | kaushik  |   3000 | 2009-10-08 00:00:00 |
|  3 | kaushik  |   1500 | 2009-10-08 00:00:00 |
|  4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
|  5 | Hardik   |   NULL | NULL                |
|  6 | Komal    |   NULL | NULL                |
|  7 | Muffy    |   NULL | NULL                |
+----+----------+--------+---------------------+

Example 2: SQL LEFT JOIN EXAMPLE :

We'll look at two tables in this example. Employee table contains information on the employees who work in a certain department, while department table contains information about the department.

employee table

emp_no	emp_name	age	salary	dept_no
E1	Varun  Singhal	27	30,000	D1
E2	Amrita Aggarwal	28	25,000	D2
E3	Ravi   Anand	30	34,000	D1
E4	Nitin  Saini	34	54,000	[NULL]
E5	Muskan Garg	35	65,000	[NULL]

department table

dept_no	dept_name	location
D1	    IT	    	Delhi
D2	    HR		    Hyderabad
D3	    FINANCE		Rajasthan

To perform left- join on these two tables we will use the following SQL query :

select emp_no ,emp_name , age, salary ,dept_name, location 
from employee 
left join department on employee.dept_no=department.dept_no;

Output :

emp_no	emp_name	 age	salary	dept_name	location
E1	  Varun Singhal	 27	    30,000	IT		    Delhi
E3	  Ravi Anand	 30	    34,000	IT		    Delhi
E2	  Amrita Singhal 28	    25,000	HR		    Hyderabad
E4	  Nitin Saini	 34	    54,000	[NULL]		[NULL]
E5	  Muskan Garg	 35	    65,000	[NULL]		[NULL]

Example 3: let us take two tables in this example to elaborate all the things:

CUSTOMER TABLE:

ID	NAME	AGE	SALARY
1	ARYAN	51	56000
2	AROHI	21	25000
3	VINEET	24	31000
4	AJEET	23	32000
5	RAVI	23	42000

This is second table

ORDER TABLE:

O_ID	DATE    	CUSTOMER_ID	AMOUNT
001	    20-01-2012	2	        3000
002	    12-02-2012	2	        2000
003	    22-03-2012	3	        4000
004	    11-04-2012	4	        5000

join these two tables with LEFT JOIN:

SQL SELECT ID, NAME, AMOUNT,DATE  
FROM CUSTOMER  
LEFT JOIN ORDER  
ON CUSTOMER.ID = ORDER.CUSTOMER_ID;  

Output:

ID	NAME	AMOUNT	DATE
1	ARYAN	NULL	NULL
2	AROHI	3000	20-01-2012
2	AROHI	2000	12-02-2012
3	VINEET	4000	22-03-2012
4	AJEET	5000	11-04-2012
5	RAVI	NULL	NULL

Example 4: Following an OUTER JOIN with these mentioned tables, the following SQL statement may be used to extract business name and company id columns from company table and company id, item name, item unit columns from foods table:

Sample table: foods

+---------+--------------+-----------+------------+
| ITEM_ID | ITEM_NAME    | ITEM_UNIT | COMPANY_ID |
+---------+--------------+-----------+------------+
| 1       | Chex Mix     | Pcs       | 16         |
| 6       | Cheez-It     | Pcs       | 15         |
| 2       | BN Biscuit   | Pcs       | 15         |
| 3       | Mighty Munch | Pcs       | 17         |
| 4       | Pot Rice     | Pcs       | 15         |
| 5       | Jaffa Cakes  | Pcs       | 18         |
| 7       | Salt n Shake | Pcs       |            |
+---------+--------------+-----------+------------+

Sample table: company

+------------+---------------+--------------+
| COMPANY_ID | COMPANY_NAME  | COMPANY_CITY |
+------------+---------------+--------------+
| 18         | Order All     | Boston       |
| 15         | Jack Hill Ltd | London       |
| 16         | Akas Foods    | Delhi        |
| 17         | Foodies.      | London       |
| 19         | sip-n-Bite.   | New York     |
+------------+---------------+--------------+

SQL Code:

SELECT company.company_id,company.company_name,
company.company_city,foods.company_id,foods.item_name
FROM   company
LEFT JOIN foods
ON company.company_id = foods.company_id;

Output:

COMPANY_ID COMPANY_NAME              COMPANY_CITY              COMPANY_ID ITEM_NAME
---------- ------------------------- ------------------------- ---------- --------------
16         Akas Foods                Delhi                     16         Chex Mix
15         Jack Hill Ltd             London                    15         Cheez-It
15         Jack Hill Ltd             London                    15         BN Biscuit
17         Foodies.                  London                    17         Mighty Munch
15         Jack Hill Ltd             London                    15         Pot Rice
18         Order All                 Boston                    18         Jaffa Cakes
19         sip-n-Bite.               New York

Explanation:

This SQL statement would return all rows from the company table and only those rows from the foods table where the joined fields are equal and if the ON clause matches no records in the 'foods' table, the join will still return rows, but the NULL in each column of the right table.

Join with same table and with table alias.

Example: I got this, hope this will be usefull to you too :

SELECT u1.USR_FNAME AS a, u1.USR_LNAME AS b, u2.USR_FNAME AS c, 
u2.USR_LNAME AS d, u3.USR_FNAME AS e, u3.USR_LNAME AS f
FROM tbl_test
LEFT JOIN tbl_users u1 ON ( tbl_test.TEST_USER1_ID = u1.USR_ID )
LEFT JOIN tbl_users u2 ON ( tbl_test.TEST_USER2_ID = u2.USR_ID )
LEFT JOIN tbl_users u3 ON ( tbl_test.TEST_USER3_ID = u3.USR_ID )
ORDER BY tbl_test.TST_ID ASC
LIMIT 10

Example 2: make life a little easier by using table aliases:

SELECT c.po_number, c.start_date, c.end_date, c.description, 
    c.taa_required, c.account_overdue, j.id AS jobs_id, j.job_number, 
    cm.id AS companies_id, cm.name AS companies_name 
FROM contracts c
LEFT JOIN jobs j ON c.job_id = j.id 
LEFT JOIN companies cm ON c.company_id = cm.id 
WHERE c.id = '$id' 
ORDER BY c.end_date

Example 3: Selecting all fields with "SELECT *" you should explicitly name each field you need, aliasing them with AS as required. For example:

SELECT si.field1 as si_field1,
       si.field2 as si_field2,
       ind_data.field1 as ind_data_field1
  FROM sites_indexed as si
  LEFT JOIN individual_data as ind_data 
         ON si.id = ind_data.site_id 
 WHERE `url` LIKE :url

And then you can reference the aliased names in your result set.

Example 1: use CASE in a join condition. I am working in Teradata so I cannot use "DECODE".

LEFT JOIN V610413.TEAM_ASSIGNMENTS ta
--ON c.firm_id = ta.FirmId
ON CASE WHEN ta.FirmId = c.firm_id THEN ta.FirmId = c.firm_id
  WHEN ta.FirmId <> c.firm_id THEN ta.FirmId = 'No Value'
  END

AND c.stateId = ta.stateId

Example 2: CASE statement in LEFT JOIN:

SELECT  a.*, 
        CASE WHEN b.totalCount = 1 AND b.totalINC = 0 THEN 'Complete'
             WHEN totalCount IS NULL THEN ''
             ELSE 'Incomplete'
        END STatus
FROM    table1 a
        LEFT JOIN
        (
            SELECT  UserID, 
                    COUNT(DISTINCT STATUS) totalCount,
                    SUM(CASE WHEN status <> 100 THEN 1 ELSE 0 END) totalINC
            FROM table2
            GROUP BY UserID
        ) b ON a.UserID = b.UserID;

Example 3:

SELECT  a.*, 
        CASE WHEN b.totalCount = 1 AND b.totalINC = 0 THEN 'Complete'
             WHEN totalCount IS NULL THEN ''
             ELSE 'Incomplete'
        END STatus
FROM    table1 a
        LEFT JOIN
        (
            SELECT  UserID, 
                    COUNT(DISTINCT STATUS) totalCount,
                    SUM(CASE WHEN status = 'Incomplete' THEN 1 ELSE 0 END) totalINC
            FROM table2
            GROUP BY UserID
        ) b ON a.UserID = b.UserID

The COUNT (*) function returns a number of rows in a specified table or view that includes the number of duplicates and NULL values. To return the number of rows that excludes the number of duplicates and NULL values.

Example 1: COUNT the LEFT JOIN with columns:

SELECT a.articleid, 
          COUNT(*) AS num_comments
     FROM ARTICLES a
LEFT JOIN COMMENTS c ON c.articleid = a.articleid
 GROUP BY a.articleid;

Because the ARTICLES table doesn't have an aggregate function applied to it, you'll have to define any columns you want in the GROUP BY clause.

Example 2: left join and count:

SELECT article_column_1, 
article_column_2, count( ct.articleid) as comments
FROM
   article_table at
   LEFT OUTER JOIN comment_table ct ON at.articleid = ct.articleid
GROUP BY 
   article_column_1, article_column_2

Example 2: mysql select and count left join:

select t.Topic,
  t.Title,
  count(distinct s.starID) as StarCount,
  count(distinct m.User) as UserCount,
  count(distinct m.messageID) as MessageCount
from
  Topics t
  left join Messages m ON m.Topic = t.Topic
  left join Stars_Given s ON s.Topic = t.Topic
group by
  t.Topic,
  t.Title

Example 1: SELECT DISTINCT with LEFT JOIN

selects IDs of all records of the same type, with the same dtIn date, ordered by stOut in ascending order:

SELECT DISTINCT tbl.id FROM tbl   
  LEFT JOIN tbl AS t1
  ON tbl.type = t1.type AND
     tbl.dtIn = t1.dtIn
  ORDER BY tbl.dtOut ASC

But it gives me an error:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Example 2: postgresql left join distinct on:

Select Distinct On (u.username, u.email)
     u.username
    ,u.email
    ,l.browser
    ,l.login_time
From users u
Join logins l On l.username = u.username
Order By u.username, u.email, login_time Desc

The GROUP BY clause can also be used with the Left Join.

Example 1: LEFT JOIN and GROUP BY :

SELECT "results".*, "workouts".*,"max_score".*
FROM "results" 
LEFT JOIN "workouts" ON "workouts"."id" = "results"."workout_id"
LEFT JOIN (SELECT user_id, workout_id, MAX(score) 
    FROM results WHERE user_id IN (1, 2) AND workout_id IN (1, 2, 3) 
    GROUP BY user_id, workout_id) max_score ON workouts.workout_id=max_score.workout_id;

Example 2: The Left Join clause combined with the GROUP BY clause gives the following information: customer id, customer name, qualification, price, and date.

SELECT customers.customer_id, cust_name, qualification, price, date  
FROM customers  
LEFT JOIN orders ON customers.customer_id = orders.customer_id  
GROUP BY price;

Output:

ID          name       salary
----------- ---------- -----------
          1 Jason             1234
          2 Robert            4321
          3 Celia             5432
          4 Linda             3456
          5 David             7654
          6 James             4567
          7 Alison            8744
          8 Chris             9875
          9 Mary              2345

select * from job;
 GO

Output:

ID          title      averageSalary
----------- ---------- -------------
          1 Developer           3000
          2 Tester              4000
          3 Designer            5000
          4 Programmer          6000

SELECT count(e.id)
FROM Employee e LEFT JOIN job j
ON e.ID = j.ID
group by j.ID
ORDER BY j.ID
GO

Output:

-----------
          5
          1
          1
          1
          1

If you need to LEFT JOIN more than two tables to obtain the information you need for a certain analysis.

In SQL, we usually use Join to create a new table. The order in which we provide tables is more important, the tables from which we need to collect all records, tuples, or rows from the left table, and only those records corresponding from the right table.

The LEFT JOIN procedure in SQL cascades across all joins in a query, which means If you utilize an LEFT JOIN, the following tables should often be left-joined as well.

The maximum number of tables that can be joined in a single SQL statement is determined by the RDMS. A connect with more than four or five tables, on the other hand, is not recommended. The number of tables supplied in the FROM clause determines the order of magnitude and complexity of the join.

Syntax:

SQL Left Join multiple tables Syntax

Here, Table1 would be consider as a left table and Table2 would be consider as a right table. similarly for second left join Table2 is considered as left table for table 3 , Table1.column1, Table1.column2, Table2.column1, are the name of the columns which you want to retrieve separated by comma.

SELECT Table1.column1, Table1.column2, Table2.column1, Table2.column2,Table3.column1,.. 
FROM Table1 LEFT OUTER JOIN Table2 ON Table1.columnname=Table2.columnname 
LEFT OUTER JOIN Table3 ON Table2.columnname=Table3.columnname..

Example 1: SQL Left Join with three tables example:

To get the combine records of patients, doctors and their laboratory use SQL Left join multiple tables.

SELECT dbo.patient.patient_id, 
dbo.patient.name AS 'Name', dbo.doctor.name AS 'Doctor name', 
dbo.patient.age, dbo.patient.gender, dbo.patient.address, 
dbo.patient.disease, dbo.doctor.address AS 'Doctor Address', 
dbo.doctor.city, dbo.laboratory.lab_no, dbo.laboratory.date, dbo.laboratory.amount 
FROM   dbo.patient 
LEFT OUTER JOIN dbo.laboratory ON dbo.patient.patient_id = dbo.laboratory.patient_id 
LEFT OUTER JOIN dbo.doctor ON dbo.laboratory.doctor_id = dbo.doctor.doctor_id 

The above Left Outer join on three tables query provides a set of all records from the patient table, including patient id, patient name, doctor name, patient age, patient gender, patient address, patient diseases, and matching records from the doctor and laboratory tables, including doctor address, doctor city with lab no, lab report date, and lab report amount.

Example 2: You can refer same 2 tables with following additional table for fetching data in 3 tables.

Employee Table:

Employee Id	Employee Name	Department Id
1		    Amit	    	233
2		    Saideepti	    233
3		    Purna		    244
4		    Rahul		    245

Department Table:

Department ID	Department Name
233 		    Business Intelligence
234		        Development
244	    	    Application support
235		        Database support

Salary Table :

Employee Id	Salary
1   		89000
2	    	60000
3	    	45000
4		    50000

Problem Statement:

We need to fetch data from Employee table with its salary and associated department. If Department is not associated with Employee need to show that Blank.

Select A.Employee_Name,B.Salary,C.Department_Name
From Employee A
Left Join
Salary B on A.Employee_Id=B.Employee_Id
Left Join
Department C On A.Department_Id = C.Department_ID;

Output :

Employee Name	Salary	Department Name
Amit		89000	Business Intelligence
Saideepti	60000	Business Intelligence
Purna		45000	Application Support
Rahul		50000	 

Query Explanation Step-by-Step :

• Step 1 : To retrieve Employee and its associated Salary, the Employee and Salary tables are joined together.
• Step 2 : To obtain the department linked with an employee, use that set and join it to the Department table.

Example 3:

Consider two tables:

1. Employee (Left Table) :

Emp_Id	First_Name	Last_Name	Gender	Age	Date_of_join
1   	Pranay		Thanneru	M	    45	2000-10-09
2	    Santhosh	Prabhu		M	    48	1999-07-10
3	    Mary		clara		F	    34	2008-08-26
4	    Jane		Vatsal		F	    30	2006-02-31
5	    Hardik		prabhu		M	    22	2012-07-23

2. Projects (Right Table) :

id	date		Project_No	Emp_id	No_of_hours_worked
1	2005-03-15	147		    3	    162
2	2005-03-16	232		    2	    192
3	2005-03-17	276		    1	    198

To Join these two tables and to obtain common information we need to use the following query

SELECT E.Emp_id, E.First_Name, E.Last_Name, E.Gender, E.age, E.Date_of_join,    
       P.date AS Project_Assigned_date, P.No_of_hours_worked AS hours_worked
FROM Employee E
LEFT JOIN Projects P
ON E.Emp_id = P.Emp_id
GROUP BY E.Emp_id;

Once after obtaining the table as you can see that the Emp_id who is not assigned for a project who’s Project_Assigned_date has became NULL and No_of_hours_worked also became NULL cause the Employee has not assigned anything to do.

Left Join means that, based on the above tables, it collected data from both table rows that match and returned NULL values for the rows whose data is not present in Table 2 since we need to consider all of the data in the Left table.

Example 4: Consider the following scenario. We'd like to look at how our recent promotional campaign has influenced our customers behaviour.

id	campaign	customer_id	date
1	SMS_discount10	2		2019-09-01
2	SMS_discount10	3		2019-09-01
3	SMS_discount10	5		2019-09-01

To do this, we need to combine the data about customers, sales, and promotions.

SELECT c.id, c.first_name, c.last_name, c.gender, c.age, c.customer_since,
   s.date AS sale, p.date AS promotion
FROM customers c
LEFT JOIN sales s
ON c.id = s.customer_id
LEFT JOIN promotions p
ON c.id = p.customer_id;

Output:

id	first_name	last_name	gender	age	customer_since	sale		promotion
1	Daniel		Black		M	    34	2014-10-13	    [NULL]		[NULL]
2	Erik		Brown		M	    25	2015-06-10	    2019-10-01	2019-09-01
3	Diana		Trump		F	    39	2015-10-25	    2019-09-02	2019-09-01
4	Anna		Yao		    F	    19	2017-02-20	    2019-10-01	[NULL]
5	Christian	Sanders		M	    42	2018-01-31	    [NULL]		2019-09-01

As you can see, we kept track of all of our clients via an LEFT JOIN, independent of purchase history or participation in promotional programmes. Customer 1 is, for example, a result of the join despite having made no transactions or received the promotional mail. Customer 4 has purchased a book but has not gotten any promotional messages, and Customer 5 has received a promotional message but has not purchased anything. Finally, the outcome includes customers who have made purchases and received promotional communications (Customers 2 and 3).

Example 1: Writing a query that joins two tables and selects only first matches from the second table for every result from the first table is an old task for SQL developers. In my scenario, I need to merge the client's initial phone number from the Phones field with his or her name from the Clients table.

After doing some research, I came to three different conclusions.

1. left Join with SELECT TOP 1 subquery

SELECT c.ClientName, ph.PhoneNumber
FROM Clients c 
LEFT JOIN Phones ph ON c.ClientGuid = ph.ClientGuid
  AND ph.PhoneNumber = (
    SELECT TOP 1 p.PhoneNumber
    FROM Phones p 
    WHERE p.ClientGuid = c.ClientGuid 
    ORDER BY p.PhonePriority
    );

Example 2: I'm running a query against a lot of enormous tables (rows and columns) with a lot of joins, but one of the tables contains some duplicate rows of data, which is causing problems for my query. I can't fix the data because it's a read-only realtime feed from another department, but I'm attempting to avoid problems in my query because of it. used in a left join, see below:

select ...
from x
  left join (
    select IDNo,
           FirstName,
           LastName,
           ....,
           row_number() over (partition by lower(idno) order by firstname) as rn 
    from people 
  ) p on p.idno = x.idno and p.rn = 1

The sql left join joins tables, but we just utilise one column here.

Syntax:

mysql join only one column

SELECT table1.*, table2.first_name FROM table1
LEFT JOIN table2 ON table1.id = table2.fkid;

Example 1: Following an OUTER JOINING with these mentioned tables, the following SQL statement can be used to extract business name and company id columns from company table and company id, item name, item unit columns from foods table:

Sample table: foods

+---------+--------------+-----------+------------+
| ITEM_ID | ITEM_NAME    | ITEM_UNIT | COMPANY_ID |
+---------+--------------+-----------+------------+
| 1       | Chex Mix     | Pcs       | 16         |
| 6       | Cheez-It     | Pcs       | 15         |
| 2       | BN Biscuit   | Pcs       | 15         |
| 3       | Mighty Munch | Pcs       | 17         |
| 4       | Pot Rice     | Pcs       | 15         |
| 5       | Jaffa Cakes  | Pcs       | 18         |
| 7       | Salt n Shake | Pcs       |            |
+---------+--------------+-----------+------------+

Sample table: company

+------------+---------------+--------------+
| COMPANY_ID | COMPANY_NAME  | COMPANY_CITY |
+------------+---------------+--------------+
| 18         | Order All     | Boston       |
| 15         | Jack Hill Ltd | London       |
| 16         | Akas Foods    | Delhi        |
| 17         | Foodies.      | London       |
| 19         | sip-n-Bite.   | New York     |
+------------+---------------+--------------+

Output:

COMPANY_ID COMPANY_NAME              COMPANY_CITY              COMPANY_ID ITEM_NAME
---------- ------------------------- ------------------------- ---------- --------------
16         Akas Foods                Delhi                     16         Chex Mix
15         Jack Hill Ltd             London                    15         Cheez-It
15         Jack Hill Ltd             London                    15         BN Biscuit
17         Foodies.                  London                    17         Mighty Munch
15         Jack Hill Ltd             London                    15         Pot Rice
18         Order All                 Boston                    18         Jaffa Cakes
19         sip-n-Bite.               New York

Explanation:

This SQL statement would return all rows from the company table and only those rows from the foods table where the joined fields are equal and if the ON clause matches no records in the 'foods' table, the join will still return rows, but the NULL in each column of the right table.

Example 1: Mysql LEFT JOINing same table twice

SELECT * FROM customers;

+----+------+
| id | name |
+----+------+
|  1 | Matt |
|  3 | John |
+----+------+

SELECT * FROM purchases;

+----------+------+-------+
| owner_id | type | quant |
+----------+------+-------+
|        3 | cat  |     3 |
|        3 | cat  |     2 |
|        1 | cat  |     4 |
|        1 | dog  |     1 |
|        1 | dog  |     2 |
+----------+------+-------+

So I have two tables and want to see a column with the customer's name, then a column with the total number of dogs purchased, and then a column for cats, and this is what I ended up with.

SELECT c.name, sum(dogs.quant) AS dogs, SUM(cats.quant) AS cats
FROM customers AS c
LEFT JOIN purchases AS dogs ON c.id=dogs.owner_id AND dogs.type = 'dog'
LEFT JOIN purchases AS cats ON c.id=cats.owner_id AND cats.type = 'cat'
GROUP BY c.name;

Output:

+------+------+------+
| name | dogs | cats |
+------+------+------+
| John | NULL |    5 |
| Matt |    3 |    8 |
+------+------+------+

Example 2: The staffs table twice: one as e for the employees and the other as m for the managers. The join predicate matches employee and manager relationship using the values in the e.manager_id and m.staff_id columns.

Because of the INNER JOIN effect, Fabiola Jackson does not appear in the employee column. If you substitute the INNER JOIN clause in the following query with the LEFT JOIN clause, you'll obtain the following result set, which includes Fabiola Jackson in the employee column:

SELECT
    e.first_name + ' ' + e.last_name employee,
    m.first_name + ' ' + m.last_name manager
FROM
    sales.staffs e
LEFT JOIN sales.staffs m ON m.staff_id = e.manager_id
ORDER BY
    manager;

Because no indexes may be used on a temporary table in memory, using subqueries in JOIN operations should typically be avoided if you can rewrite the query in a different way. utilising a left join on a subquery to get a list of towns

MySQL's ability to correlate subqueries is severely limited. A correlated value, for example, cannot be nested more than one level deep, nor can it be utilised in a join's ON clause. As a result, we'll need to add an extra join to our subqueries.

It's worth noting that the join keyword's left and right tables must both yield a common key that may be used to join the tables.

Example 1: In Access, I can create a query (tblCurrentNamesInTown):

SELECT tblNames.TownID
FROM tblNames
WHERE (((tblNames.ELECTIONID)=1))

then a new query (tblTownData Without Matching Current):

SELECT tblTownData.Municipality
FROM tblTownData LEFT JOIN tblCurrentNamesInTown ON tblTownData.TownID =
tblCurrentNamesInTown.TownID
WHERE (((tblCurrentNamesInTown.TownID) Is Null));

Example 2: We'll need to join game to itself in the subqueries and utilise the values of a joined table rather than the correlated values to rewrite the aggregate query as subqueries:

SELECT  g.id, g.name,
(
SELECT  GROUP_CONCAT(p.name SEPARATOR ', ') AS players
FROM    game gi
LEFT JOIN
player_team pt
ON      gi.type = 'team'
AND pt.team = gi.player1
JOIN    player p
ON      p.id = CASE gi.type WHEN 'player' THEN gi.player1 WHEN 'team' THEN pt.player END
WHERE   gi.id = g.id
),
(
SELECT  GROUP_CONCAT(p.name SEPARATOR ', ') AS players
FROM    game gi
LEFT JOIN
player_team pt
ON      gi.type = 'team'
AND pt.team = gi.player2
JOIN    player p
ON      p.id = CASE gi.type WHEN 'player' THEN gi.player2 WHEN 'team' THEN pt.player END
WHERE   gi.id = g.id
)
FROM    game g
ORDER BY
id
LIMIT 50

Example 3: We've taken the tables Movies and movie_genre, which we built previously, as samples. Let's have a look at the query:

With subquery AS (
SELECT genre_id, movie_genre
FROM Movie_genres
)
UPDATE Movies
SET movie_genre = subquery.movie_genre
FROM Movies AS m
LEFT JOIN subquery on m.genre_id = subquery.genre_id
WHERE Movies.movie_id = m.movie_id;

Example 1: You're combining table 'goods' with two other tables, each of which has a one-to-many relationship with the 'goods' table. When they're combined, a new set of rows appears, so if there are two photos, shop products will appear twice.

The simplest approach to handle this is to determine the statistics of the sub-tables before joining them and counting unique items using distinct counting, therefore your query should be:

,SELECT good.id, good.title, sum_rest AS storerest, count(distinct pics.id) AS picscount 
FROM goods 
LEFT JOIN (select goodid, sum(rest) as sum_rest from store) s ON (goods.id = s.goodid) 
LEFT JOIN pics ON (goods.id = pics.goodid) 
GROUP BY goods.id

Example 2: Calculating the fuel consumption grouped by the column CarType. To get the fuel consumption I want to calculate the distance and fuel quantity for each car, then sum that up grouped by the column CarType.

SELECT DISTINCT C.CarType AS [Car type],
SUM(M.MaintenanceCost) AS [Maintenance],
SUM(F.Cost) AS [Fuel],
(MAX(Odometer)-MIN(Odometer)) AS [Distance], 
(SUM(Quantity)*100)/(MAX(Odometer)-MIN(Odometer)) AS [L/100km]
FROM Cars AS C 
LEFT JOIN Maintenances AS M ON M.CarID=C.CarID 
AND M.MaintenanceDate BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:29'
LEFT JOIN Fuelings AS F ON F.CarID=C.CarID 
AND F.FuelingDate BETWEEN '2021-01-01 00:00:00' AND '2021-01-31 23:59:29'
GROUP BY C.CarType

Example 3: Left join with sum clause:

SELECT members.id AS aid, 
       ad.deduction_amount, 
       ac.total_portion 
FROM members 
LEFT JOIN ( SELECT agents_deductions.agent_id, SUM(agents_deductions.amount) deduction_amount
            FROM agents_deductions
            WHERE agents_deductions.loan_status = 0
            GROUP BY agents_deductions.agent_id
          ) ad ON members.id = ad.agent_id
LEFT JOIN ( SELECT agents_commission.agent_id, SUM(agents_commission.portion) total_portion
            FROM agents_commission
            WHERE agents_commission.loan_status = 0
            GROUP BY agents_commission.agent_id
          ) ac ON members.id = ac.agent_id
ORDER BY members.id

agents_data table is included in the query data source. None of its field is used in a query (except join condition) and it cannot affect to the query result because of left join (except unwanted multiplying).

Example 4:

SELECT userid, 
       awardtypeid, 
       SUM(awardamount) 
FROM   awards a 
       LEFT JOIN userinfo ui 
         ON ui.userid = a.userid 
       LEFT JOIN awardtypes 
         ON awardtypesid = a.awardtypeid 
GROUP  BY userid, 
          awardtypeid 

or

SELECT userid, 
       SUM(awardamount) 
FROM   awards a 
       LEFT JOIN userinfo ui 
         ON ui.userid = a.userid 
       LEFT JOIN awardtypes 
         ON awardtypesid = a.awardtypeid 
GROUP  BY userid

The id Column is now removed (probably not what you want to group on), I included the awardtypeid in the choose in the first case, but you must also include it in the group by.

Example 1: SQL UNION ALL LEFT JOIN:

SELECT comments.*, 
users.company, users.contact_person, users.email
  FROM
    comments
    LEFT JOIN users ON users.user_id = comments.user_id
    WHERE
        comment_id = %s
UNION ALL
SELECT activity.*, users.company, users.contact_person, users.email
    FROM
        activity
    LEFT JOIN users ON users.user_id = activity.user_id
    WHERE
        comment_id = %s
ORDER BY
    timestamp ASC

Example 2: Subquery that you've named 'bl_bands' doesn't have bl_bands.id because all the union joins don't include the bl_bands table. Try adding joins to each union, if my assumption on all your data is correct:

SELECT bl_albums.*,
    bl_bands.name as bandname,
    bl_bands.id as bandid, 
    bl_bands.bandpage as bandpage,
    sum(relevance)
FROM
    bl_albums 
    LEFT JOIN(
      SELECT  bl_albums.*, bl_bands.name as bandname, bl_bands.id as bandid, 
              bl_bands.bandpage as bandpage,10 AS relevance 
      FROM bl_albums
        JOIN bl_bands ON bl_bands.id = bl_albums.bandid 
      WHERE bl_albums.name like 'Camera'
      UNION
      SELECT bl_albums.*, bl_bands.name as bandname, bl_bands.id as bandid, 
             bl_bands.bandpage as bandpage, 7 AS relevance 
      FROM bl_albums 
        JOIN bl_bands ON bl_bands.id = bl_albums.bandid
      WHERE bl_albums.name like 'Camera%'
      UNION
      SELECT  bl_albums.*, bl_bands.name as bandname, bl_bands.id as bandid, 
              bl_bands.bandpage as bandpage, 5 AS relevance 
      FROM bl_albums 
        JOIN bl_bands ON bl_bands.id = bl_albums.bandid
      WHERE bl_albums.name like '%Camera'
      UNION
      SELECT bl_albums.*, bl_bands.name as bandname, bl_bands.id as bandid, 
             bl_bands.bandpage as bandpage, 2 AS relevance 
      FROM bl_albums 
        JOIN bl_bands ON bl_bands.id = bl_albums.bandid
      WHERE bl_albums.name like '%Camera%'
    ) bl_bands ON bl_albums.bandid = bl_bands.id
GROUP BY bl_albums.name
ORDER BY relevance desc

It appears that you copied/pasted several SELECT statements/column names but did not include the joining required to obtain the results.

When you need all records from the "left" table, regardless of whether they have a pair in the "right" table, use INNER JOIN, and when you need all records from the "right" table, use LEFT JOIN. You'll need to use CROSS JOIN if you need all records from both tables, regardless of whether they have pair (or simulate it using LEFT JOINs and UNION).

INNER JOIN

Inner join only returns matched rows from both tables involved in the join; non-matching rows are removed.

The INNER JOIN command selects only rows that satisfy an ON condition. There will be no results displayed if there are no rows that match the ON condition.

Example 1: Inner joins two tables together to find common records. We have "ClassID" in the tclass database and "ClassID" in the tstudent table in the instance above. The following query is used to get a list of students in Class "10th."

SELECT s.StudentID, s.StudentName
FROM tClass c 
INNER JOIN tstudent s ON c.ClassID = s.ClassID
WHERE c.ClassID = 10

As a result, the aforementioned query is used to retrieve a list of students in Class – "10th." So all students are saved in the "tstudent" table, regardless of their classes, and all classes are saved in the "tclass" table. As a result of the query, matched records from the tstudent and tclass tables are found and shown. The term "predicate" was introduced in the introduction chapter; it is "ON c.ClassID = s.ClassID" in the above query, which is an important element of the join.

Note : There are multiple keys associated to each table like in tStudent table have “StudentID” as a primary key field and “ClassID” as a foreign key which inturn refer to the table – “tClass”.

The above query can be rewritten without using inner join like below but the performance will be impacted compared to inner join :

SELECT s.StudentID, s.StudentName
FROM tClass c , tstudent s
WHERE c.ClassID = s.ClassID AND c.ClassID = 10

LEFT JOIN

Only the matching rows from both tables, containing non-matching rows, as well as non-matching rows from the left table, are returned in the Left Join or Left Outer Join.

LEFT JOIN returns all data from the first table, regardless of whether there are any matches with the second table, and NULL fills in the gaps.

Performance difference between INNER JOIN and LEFT JOIN

INNER JOIN and LEFT JOIN performance is affected by the number of JOINS performed and whether or not the columns are indexed. Performing entire 9 to 10 table scans for each JOIN could also slow down the operation. When some of the tables are relatively small, say less than 10 rows, or when the tables lack enough indexes to cover the query, LEFT JOIN may be faster than INNER JOIN. As a result, the circumstances are crucial.

Example: Below query is used to fetch the all the classes and the students are in that class.

SELECT s.StudentID, s.StudentName
FROM tClass c 
LEFT JOIN tstudent s ON c.ClassID = s.ClassID

Above can be rewritten using “LEFT OUTER JOIN” as :

SELECT s.StudentID, s.StudentName
FROM tClass c 
LEFT OUTER JOIN tstudent s ON c.ClassID = s.ClassID

As illustrated in the accompanying diagram, all rows from "tclass" are retrieved, as are the students of the various classes. If no students are identified in that class, the class will still be retrieved from the left table, with NULL displayed in place of "StudentID" and "StudentName."

There is no distinction between LEFT JOIN and LEFT OUTER JOIN. Despite the fact that both yield the same output and performance, I prefer to use LEFT OUTER JOIN over just LEFT JOIN. As it is more readable and leaves no misunderstanding. Please let us know which one you prefer and why.

The PostgreSQL LEFT JOIN joins two tables and retrieves results based on a criteria that is met in both tables, with unmatched rows also obtainable from the table created before the JOIN clause.

A left outer join will contain the entire set of records from the first table, together with the matching results in the related table. If no matching results are found, the right side will contain a null. The usage of a 'where' clause is used to produce only the records in the left table and not the right table.

Syntax:

Select *
FROM table1
LEFT [ OUTER ] JOIN table2
ON table1.column_name=table2.column_name;

If a record in table1 fulfils the WHERE clause but not the ON condition, an extra table2 entry is created with all columns set to NULL.

Example 1: Let us prove with examples that there is no difference between LEFT JOIN and LEFT OUTER JOIN.

LEFT JOIN

SELECT * 
FROM Customers C 
  LEFT JOIN Orders O
    ON O.CustomerId = C.CustomerId

LEFT OUTER JOIN

SELECT * 
FROM Customers C 
  LEFT OUTER JOIN Orders O
    ON O.CustomerId = C.CustomerId;

Example 2: The LEFT join in PostgreSQL retrieves the entire set of records from the left, and the matching records (depending on availability) from the right. When no matching occurs, the result is NULL on the right side.

SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM item
LEFT JOIN invoice
ON item.item_no=invoice.item_no;

OR

SELECT item.item_no,item_descrip,
invoice.invoice_no,invoice.sold_qty
FROM item
LEFT OUTER JOIN invoice
ON item.item_no=invoice.item_no;

In the previous example, the item no I8 of the item_table does not exist in the invoice table, so a new row in the invoice table was constructed for these rows of the item table and set to NULL.

LEFT JOIN / IS NULL performs worse in both cases because it either performs an extra table lookup or does not return on the first match.

NOT EXISTS is a simple function that just checks equality and returns TRUE or FALSE on the first hit or miss.

The most significant difference is (as written), the SELECT *, not the join vs. not exists.

Syntax:

LEFT JOIN SQL, "WHERE table1.a IS NULL" should be "WHERE table2.a IS NULL"

SELECT a FROM table1 

LEFT JOIN table2 

ON table1.a = table2.a 

WHERE table2.a IS NULL

LEFT JOIN / IS NULL will return TRUE only if no row in table2 matches the equality condition, so it will behave similarly to NOT EXISTS.

Example 1: Is NOT EXISTS or LEFT OUTER JOIN...IS NULL more efficient for finding records in one table that aren't in another? Specifically, I'm trying to figure out which of the two queries below is better:

SELECT table1.id FROM table1 WHERE table1.a='FOO' AND 
NOT EXISTS (SELECT 1 FROM table2 WHERE table1.id = table2.id);

or

SELECT table1.id FROM table1 LEFT OUTER JOIN table2 
ON table1.id = table2.id WHERE table1.a='FOO' AND table2.id IS NULL;

Example 2: LEFT JOIN / IS NULL

SELECT  o.*
FROM    outer o
LEFT JOIN
        inner i
ON      i.value = o.value
WHERE   i.value IS NULL

NOT EXISTS

SELECT  o.*
FROM    outer o
WHERE   NOT EXISTS
        (
        SELECT  NULL
        FROM    inner i
        WHERE   i.value = o.value
        )

Example 3: On the first example, you get all columns from both A and B, whereas in the second example, you get only columns from A.

To achhieve this Perform the two test SELECT statement variants:

SELECT *
FROM dbo.A
    LEFT JOIN dbo.B ON A.A_ID = B.B_ID
WHERE B.B_ID IS NULL;

SELECT *
FROM dbo.A
WHERE NOT EXISTS (SELECT 1
    FROM dbo.B
    WHERE b.B_ID = a.A_ID);

The LEFT JOIN command returns all records from the first left table, matched records from the second right table, and NULL values from the right side for records from the left table that do not have a match in the right table.

The WHERE clause of the SQL NOT IN command can have several values. It can compare particular column values from the first table to other column values in the second table or a subquery and return all values from the first table that aren't found in the second table, without filtering for distinct values. The NOT IN command considers NULL as a value and returns it.

Example 1: Using NOT IN or LEFT JOIN WHERE IS NULL in a SQL query achieves the same result. For instance:

SELECT a FROM table1 WHERE a NOT IN (SELECT a FROM table2)

SELECT a FROM table1 LEFT JOIN table2 ON table1.a = table2.a WHERE table1.a IS NULL

Example 2: Just for fun, choose one of the options LEFT JOIN or NOT IN. Review the following two queries for Join Better Performance if you need to refer to the query that displays the given clauses.

USE AdventureWorks;
GO

SELECT ProductID
FROM Production.Product
WHERE ProductID
NOT IN (
SELECT ProductID
FROM Production.WorkOrder);
GO

SELECT p.ProductID
FROM Production.Product p
LEFT JOIN Production.WorkOrder w ON p.ProductID = w.ProductID
WHERE w.ProductID IS NULL;
GO

The first query with a NOT IN consumes 20% of the execution plan's resources, while the LEFT JOIN consumes 80% of the execution plan's resources. In this case, the NOT IN clause is preferable to the LEFT JOIN clause. Please keep in mind that this is a specific case and not a general conclusion. Many elements can influence your outcome. Please let me know if you guessed correctly or incorrectly.

Example 3: Performance of NOT IN and LEFT JOIN:

USE SQLShackDemo 
GO
 
SET STATISTICS TIME ON
SET STATISTICS IO ON
 
--- NOT IN

SELECT Cat_ID
FROM Category_A  WHERE Cat_ID NOT IN (SELECT Cat_ID FROM Category_B)
GO

-- LEFT JOIN
 
SELECT A.Cat_ID
FROM Category_A A 
LEFT JOIN Category_B B ON A.Cat_ID = B.Cat_ID
WHERE B.Cat_ID IS NULL
GO

LEFT JOIN and RIGHT JOIN are the two main types of joins in MySQL. The inclusion of non-matched rows is the key distinction between both joins.

Both are outer joins, which produce a table including the matched data from the two tables, as well as the remaining rows of the left table and matching rows from the right. If there is no matching row on the right side table, the result-set will be null.

The right join, on the other hand, is an outer join that produces a table including the matched data from the two tables being joined, as well as the remaining rows of the right table and matching rows from the left table. If no matching row on the left side table exists, the result-set will have a null value.

When we say left join, we're talking about the left outer join, and when we say right join, we're talking about the right outer join.

Example 1: Suppose you want the following result:

Year     Star Wars movie 
1977     A New Hope 
1978     [null] 
1979     [null] 
1980     The Empire Strikes Back 
1981     [null] 
1982     [null] 
1983     Return of the Jedi 
1984     [null] 

You could do it this way:

SELECT * FROM Years LEFT OUTER JOIN StarWarsMovies USING (Year);

Or this way:

SELECT * FROM StarWarsMovies RIGHT OUTER JOIN Years USING (Year);

To put it another way, when you use LEFT OUTER JOIN, you obtain all rows from the table on the left. When you use RIGHT OUTER JOIN, you obtain all rows from the table on the right.

Example 2: When the tables are student and location, the SQL statement for left join is:

SELECT * FROM student LEFT OUTER JOIN location ON (student.ID = location.ID); 

whereas, the SQL statement for right join is:

SELECT * FROM student RIGHT OUTER JOIN location ON (student.ID = location.ID);

Example 3: LEFT JOIN Example:

Following SQL statement returns the matching records from both tables using the LEFT JOIN query:

SELECT cust_id, cust_name, order_num, order_date  
FROM customer LEFT JOIN orders   
ON customer.cust_id = orders.order_id  
WHERE order_date < '2020-04-30';  

RIGHT JOIN Example

Following SQL statement returns the matching records from both tables using the RIGHT JOIN query:

SELECT cust_id, cust_name, occupation, order_num, order_date    
FROM customer  
RIGHT JOIN orders ON cust_id = order_id    
ORDER BY order_date;  

Example 4: There are certain records in both tables that have no matching records. For example, the developer's table has a record with the name Siddharth and the team id 7, but the team's table has no record with that id. Similarly, the team's table contains records for departments CRM, Bug Solver, and Document with ids 4,5 and 6 that do not match any records in table developers.

Now, let us perform the left join on tables teams and developers in the following way:

SELECT * FROM teams t LEFT JOIN developers d ON t.id=d.team_id;

Now, let us perform the right join on tables teams and developers in the following way:

SELECT * FROM teams t RIGHT JOIN developers d ON t.id=d.team_id;

Before using LEFT JOIN, it uses the same filtering mechanism. WHERE CLAUSE can also be combined with LEFT JOIN and ON Clause for this reason.

WHERE CLAUSE filters the rows in a table or several tables, as you may know.

In an LEFT JOIN, the WHERE CLAUSE filters the records of either the LEFT Table or the RIGHT Table first, before performing the join with the ON Clause.

Syntax :

Here is the syntax of how you can use WHERE CLAUSE with SQL LEFT JOIN.

SELECT columns_name(s)
FROM Table1
LEFT JOIN Table2
ON 
Join_predicate
WHERE CLAUSEpredicate;

Example 1: Let's say we only want to join the Clients and Orders tables for customers whose accounts aren't suspended. The SELECT statement with LEFT JOIN and WHERE CLAUSE is as follows:

SELECT Customers.CName, Customers.address, Orders.orderstatus, Customers.accountstatus
FROM Customers LEFT JOIN Orders 
ON Customers.ID = Orders.CustomerID
WHERE Customers.accountstatus <>"suspended";

Example 2: use of where in left join in mysql:

SELECT bk1.book_name,bk1.isbn_no,bk1.book_price,bk1.pub_lang         
FROM  book_mast bk1          
LEFT JOIN book_mast bk2 ON bk1.book_price < bk2.book_price        
WHERE bk2.pub_lang='German';

Example 3: add a When clause to the query to extract only the data from "Table2" where the ID is less than 4, for example:

SELECT *
  FROM @Table1 tb1
        LEFT OUTER JOIN @Table2 tb2
          ON tb1.colID = tb2.columnID
 WHERE tb2.columnID < 4;

When we use a left join, the results will favour the left table, but if we only want the first few records, we may use a top 1 left join.

Example 1: There may be numerous rows with the same marker key in dps markers, but we only want to join against the first. If I remove the top 1 and ORDER BY, I receive a result for mbg.marker value, however if I execute the query as is, it always returns null.

<SELECT u.id, mbg.marker_value 
FROM dps_user u
LEFT JOIN 
    (SELECT TOP 1 m.marker_value, um.profile_id
     FROM dps_usr_markers um (NOLOCK)
         INNER JOIN dps_markers m (NOLOCK) 
             ON m.marker_id= um.marker_id AND 
                m.marker_key = 'moneyBackGuaranteeLength'
     ORDER BY m.creation_date
    ) MBG ON MBG.profile_id=u.id 
WHERE u.id = 'u162231993'

Example 2: Select TOP 1 records from LEFT JOIN with SQL:

SELECT p.ResourceID, d.ExpiryDate
FROM People p
LEFT OUTER JOIN (SELECT TOP 1 ExpiryDate, ResourceID
                 FROM Documents
                 ORDER BY ExpiryDate DESC) d
ON p.ResourceID = d.ResourceID

Output:

57, 1/1/2010
69, NULL
80, NULL
120, NULL
134, NULL

Example 3: Instead of an LEFT JOIN, the solution was to perform an OUTER APPLY. An outer apply, like a sub select, allows you to build a sub query that references the ID from the original table. Because this is essentially a join, you have access to numerous columns.

Consider a table with customers and addresses, where each customer can have many addresses.

Customer (CustomerID, FirstName, LastName)

Address (AddressID, CustomerID, Line1, Line2, Town, County, Country, DateAdded)

The goal is to obtain a list of customers as well as their most recent address. Using an OUTER APPLY, we can link the two tables and obtain the most current address as follows:

SELECT c.*, la.*
FROM Customer c
OUTER APPLY
      (SELECT TOP 1 *
      FROM Address a
      WHERE a.CustomerID = c.CustomerID
      ORDER BY a.DateAdded DESC
      ) AS la