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 FULL JOIN

A FULL JOIN in SQL returns all records when there is a match in either the left (table1) or the right (table2) table records. Essentially, it combines the functions of a LEFT JOIN and a RIGHT JOIN.

Here's the basic syntax:

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

The FULL JOIN keyword returns all records from both tables, and fills in NULL for missing matches on either side.

Example:

Consider the following two tables:

Orders table:

Customers table:

If we want to select all orders, and any matching customer information, we can use a FULL JOIN like this:

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

This would return:

As you can see, it has returned all records from both Orders and Customers. Where there are no matching values, the result is NULL.

Note: Not all database systems support FULL JOIN. However, you can achieve similar results by combining LEFT JOIN and RIGHT JOIN, or by using a UNION of a LEFT JOIN and a RIGHT JOIN.

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. Combining Rows from Multiple Tables with FULL JOIN:

   • Description: Retrieves all rows from both tables, matching rows where available and filling in NULLs for non-matching rows.
   • Code Example:

     SELECT *
     FROM Table1
     FULL JOIN Table2 ON Table1.ID = Table2.ID;
     

2. SQL FULL OUTER JOIN Example:

   • Description: FULL OUTER JOIN is another term for FULL JOIN, retrieving all rows from both tables.
   • Code Example:

     SELECT *
     FROM Table1
     FULL OUTER JOIN Table2 ON Table1.ID = Table2.ID;
     

3. FULL JOIN vs INNER JOIN vs LEFT JOIN:

   • Description: FULL JOIN returns all rows from both tables, INNER JOIN returns only matching rows, and LEFT JOIN returns all rows from the left table and matching rows from the right table.

   • Code Example (INNER JOIN):

     SELECT *
     FROM Table1
     INNER JOIN Table2 ON Table1.ID = Table2.ID;
     

   • Code Example (LEFT JOIN):

     SELECT *
     FROM Table1
     LEFT JOIN Table2 ON Table1.ID = Table2.ID;
     

4. Using WHERE Clause with FULL JOIN:

   • Description: Filters results based on conditions after the FULL JOIN operation.
   • Code Example:

     SELECT *
     FROM Table1
     FULL JOIN Table2 ON Table1.ID = Table2.ID
     WHERE Table1.Column1 = 'Value';
     

5. Handling NULL Values in SQL FULL JOIN:

   • Description: NULL values are introduced for non-matching rows, and conditions can be used to handle them.
   • Code Example:

     SELECT COALESCE(Table1.Column1, 'DefaultValue') AS ResultColumn
     FROM Table1
     FULL JOIN Table2 ON Table1.ID = Table2.ID;
     

6. Multiple Tables FULL JOIN in SQL:

   • Description: Extends the FULL JOIN operation to more than two tables.
   • Code Example:

     SELECT *
     FROM Table1
     FULL JOIN Table2 ON Table1.ID = Table2.ID
     FULL JOIN Table3 ON Table1.ID = Table3.ID;
     

7. SQL FULL JOIN with Multiple Conditions:

   • Description: Allows specifying multiple conditions for matching rows in the FULL JOIN operation.
   • Code Example:

     SELECT *
     FROM Table1
     FULL JOIN Table2 ON Table1.ID = Table2.ID AND Table1.Column1 = Table2.Column1;