MySQL Tutorial

MySQL Installation and Configuration

MySQL Database Operations

Database Design

MySQL Data Types

MySQL Storage Engines

MySQL Basic Operations of Tables

MySQL Constraints

MySQL Operators

MySQL Function

MySQL Manipulate Table Data

MySQL View

MySQL Indexes

MySQL Stored Procedure

MySQL Trigger

MySQL Transactions

MySQL Character Set

MySQL User Management

MySQL Database Backup and Recovery

MySQL Log

MySQL Performance Optimization

MySQL LEFT/RIGHT JOIN: outer join

These are types of SQL JOINs that allow you to combine rows from two or more tables based on a related column.

LEFT JOIN

A LEFT JOIN returns all the rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the right side. Here's a basic example:

Consider two tables: employees and departments.

employees table:

idnamedepartment_id
1John100
2Alice200
3Bob300
4NancyNULL

departments table:

idname
100HR
200Marketing
300Engineering

You can use a LEFT JOIN to join these two tables on the department_id and id fields like this:

SELECT employees.name, departments.name 
FROM employees 
LEFT JOIN departments ON employees.department_id = departments.id;

This will give you the following result:

namename
JohnHR
AliceMarketing
BobEngineering
NancyNULL

Notice that the employee "Nancy" appears in the result, even though there is no matching department for her. This is because LEFT JOIN includes all records from the left table (employees), even if there is no match in the right table (departments).

RIGHT JOIN

A RIGHT JOIN works exactly the opposite way of a LEFT JOIN. It returns all the rows from the right table, and the matched rows from the left table. If there is no match, the result is NULL on the left side. Here's an example with the same tables:

SELECT employees.name, departments.name 
FROM employees 
RIGHT JOIN departments ON employees.department_id = departments.id;

This will give you the following result:

namename
JohnHR
AliceMarketing
BobEngineering
NULLNULL

This time, all departments are included in the result, even if there is no matching employee for them.

Keep in mind, MySQL supports several types of JOINs including LEFT JOIN, RIGHT JOIN, INNER JOIN and FULL JOIN, each with their own specific use cases. You should choose the type of JOIN based on what you need for your specific situation.

  1. MySQL 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.
    • Example Code:
      SELECT * FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

  2. How to Use LEFT JOIN in MySQL:

    • Description: Use the LEFT JOIN keyword in your SQL query to retrieve all records from the left table and matching records from the right table.
    • Example Code:
      SELECT column1, column2, ...
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

  3. Left Outer Join in MySQL:

    • Description: LEFT OUTER JOIN is synonymous with LEFT JOIN. It returns all records from the left table and the matched records from the right table. If there is no match, NULL values are returned.
    • Example Code:
      SELECT * FROM table1
LEFT OUTER JOIN table2 ON table1.column_name = table2.column_name;

  4. Joining Multiple Tables Using LEFT JOIN in MySQL:

    • Description: You can join more than two tables using multiple LEFT JOIN clauses in your SQL query.
    • Example Code:
      SELECT * FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name
LEFT JOIN table3 ON table1.column_name = table3.column_name;

  5. Filtering LEFT JOIN Results in MySQL:

    • Description: Apply additional conditions to filter the results of a LEFT JOIN using the WHERE clause.
    • Example Code:
      SELECT * FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name
WHERE table2.column_name IS NOT NULL;

  6. MySQL RIGHT JOIN Example:

    • Description: The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side when there is no match.
    • Example Code:
      SELECT * FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

  7. How to Use RIGHT JOIN in MySQL:

    • Description: Utilize the RIGHT JOIN keyword in your SQL query to retrieve all records from the right table and matching records from the left table.
    • Example Code:
      SELECT column1, column2, ...
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

  8. Right Outer Join in MySQL:

    • Description: RIGHT OUTER JOIN is synonymous with RIGHT JOIN. It returns all records from the right table and the matched records from the left table. If there is no match, NULL values are returned.
    • Example Code:
      SELECT * FROM table1
RIGHT OUTER JOIN table2 ON table1.column_name = table2.column_name;
Copyright © devanddep.com | All Rights Reserved