Day 8: Working with Joins

Day 8: Working with Joins

    1. INNER JOIN 

            Returns rows with matching values in both tables.
                 

            Syntax:

                    SELECT Table1.Column1, Table2.Column2
                    FROM Table1
                    INNER JOIN Table2
                    ON Table1.CommonColumn = Table2.CommonColumn;

            Example:

                SQL

            SELECT Employees.Name, Departments.DepartmentName
            FROM Employees
            INNER JOIN Departments
            ON Employees.DepartmentID = Departments.DepartmentID;

    2. LEFT JOIN (or LEFT OUTER JOIN)

            Returns all rows from the left table and matching rows from the right table. Non-matching rows in the right table return NULL.

            Example:

                SQL

            SELECT Employees.Name, Departments.DepartmentName
            FROM Employees
            LEFT JOIN Departments
            ON Employees.DepartmentID = Departments.DepartmentID;

    3. RIGHT JOIN (or RIGHT OUTER JOIN)

            Returns all rows from the right table and matching rows from the left table. Non-matching rows in the left table return NULL.

            Example:

                SQL

            SELECT Employees.Name, Departments.DepartmentName
            FROM Employees
            RIGHT JOIN Departments
            ON Employees.DepartmentID = Departments.DepartmentID;

    4. FULL JOIN (or FULL OUTER JOIN)

            Returns all rows when there is a match in either table. Rows without matches return NULL.

        Example:

            SQL

        SELECT Employees.Name, Departments.DepartmentName
        FROM Employees
        FULL JOIN Departments
        ON Employees.DepartmentID = Departments.DepartmentID;

    5. SELF JOIN

            A SELF JOIN is a table joined with itself, useful for hierarchical or relationship data.

        Syntax:

            SELECT A.Column1, B.Column2
            FROM TableName A
            INNER JOIN TableName B
            ON A.CommonColumn = B.CommonColumn;

        Example:

            SQL

        -- Find employees with the same manager
        SELECT A.Name AS Employee, B.Name AS Manager
        FROM Employees A
        INNER JOIN Employees B
        ON A.ManagerID = B.EmployeeID;

    6. CROSS JOIN
             

            Combines each row from the first table with all rows from the second table, creating a Cartesian product.

            Syntax:

                SELECT * FROM Table1 CROSS JOIN Table2;

            Example:

                SQL

            SELECT Employees.Name, Projects.ProjectName FROM Employees CROSS JOIN Projects;

Combining Joins with Filters

        Use WHERE or ON to refine join results.

        Example:

            SQL

        -- Employees without departments
        SELECT Employees.Name, Departments.DepartmentName
        FROM Employees
        LEFT JOIN Departments
        ON Employees.DepartmentID = Departments.DepartmentID
        WHERE Departments.DepartmentName IS NULL;

Action Steps

1. Create two related tables (e.g., Employees and Departments).
2. Practice each join type (INNER, LEFT, RIGHT, FULL).
3. Write queries combining joins with filtering conditions.