SQL Tutorial

• What is SQL
• What is RDBMS
• Commonly Used Relational Databases
• SQL Syntax
• SQL Data Types
• SQL CREATE DATABASE
• SQL DROP DATABASE
• SQL USE
• SQL CREATE TABLE
• SQL DROP TABLE
• SQL RENAME TABLE
• SQL INSERT INTO
• SQL SELECT
• SQL WHERE
• SQL AND and OR
• SQL UPDATE
• SQL DELETE
• SQL LIKE
• SQL TOP
• SQL ORDER BY
• SQL GROUP BY
• SQL DISTINCT
• SQL Constraints
• SQL NULL
• SQL NOT NULL
• SQL DEFAULT
• SQL UNIQUE
• SQL Primary Key
• SQL Foreign Key
• SQL CHECK
• SQL INDEX
• SQL JOIN
• SQL INNER JOIN
• SQL LEFT JOIN
• SQL RIGHT JOIN
• SQL FULL JOIN
• SQL SELF JOIN
• SQL CROSS JOIN
• SQL UNION and UNION ALL
• SQL AS
• SQL ALTER TABLE
• SQL TRUNCATE TABLE
• SQL View
• SQL HAVING
• SQL Transaction
• SQL Temporary Tables
• SQL Clone Table
• SQL Sub Query
• SQL AUTO_INCREMENT
• SQL Injection

SQL LEFT JOIN

The LEFT JOIN keyword in SQL is used to return all records from the left table (Table1), and the matched records from the right table (Table2). If there is no match, the result is NULL on the right side.

Here's the basic syntax for a LEFT JOIN:

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example:

Consider the following two tables:

Orders table:

Customers table:

We can use LEFT JOIN to combine these tables based on the CustomerID:

SELECT Customers.Name, Orders.OrderID, Orders.OrderAmount
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

The result would be:

In this result set, you can see that for customer 'Bob' there are no matching orders, so the OrderID and OrderAmount fields for Bob are NULL.

Note:

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL on the right side when there is no match.

As always, the exact syntax may vary between different SQL dialects, so be sure to consult the documentation for the SQL dialect you're using.

1. SQL LEFT JOIN Example:

   • Description: The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side if there is no match.
   • Code Example:

     SELECT *
     FROM table1
     LEFT JOIN table2 ON table1.column_name = table2.column_name;
     

2. LEFT JOIN vs INNER JOIN in SQL:

   • Description: The main difference is that INNER JOIN returns only the matching rows, while LEFT JOIN returns all rows from the left table and the matching rows from the right table.
   • Code Example:

     -- INNER JOIN example
     SELECT *
     FROM table1
     INNER JOIN table2 ON table1.column_name = table2.column_name;
     
     -- LEFT JOIN example
     SELECT *
     FROM table1
     LEFT JOIN table2 ON table1.column_name = table2.column_name;
     

3. Using WHERE Clause with LEFT JOIN:

   • Description: You can use the WHERE clause to further filter the result of a LEFT JOIN.
   • Code Example:

     SELECT *
     FROM table1
     LEFT JOIN table2 ON table1.column_name = table2.column_name
     WHERE table2.column_name IS NULL;
     

4. Handling NULL Values in LEFT JOIN:

   • Description: When there is no match in the right table, the columns from the right table will contain NULL values. You can handle them using IS NULL or COALESCE.
   • Code Example:

     SELECT table1.column1, COALESCE(table2.column2, 'N/A') AS column2
     FROM table1
     LEFT JOIN table2 ON table1.column_name = table2.column_name;
     

5. Multiple Conditions in LEFT JOIN Statement:

   • Description: You can specify multiple conditions for the LEFT JOIN to ensure a more specific match.
   • Code Example:

     SELECT *
     FROM table1
     LEFT JOIN table2 ON table1.column1 = table2.column1 AND table1.column2 = table2.column2;
     

6. LEFT JOIN with Multiple Tables in SQL:

   • Description: You can LEFT JOIN more than two tables by extending the JOIN clause.
   • Code Example:

     SELECT *
     FROM table1
     LEFT JOIN table2 ON table1.column_name = table2.column_name
     LEFT JOIN table3 ON table1.column_name = table3.column_name;