Home - News ( United States | United Kingdom | Italy | Germany ) - Football scores

Showing content from https://www.geeksforgeeks.org/mysql/how-to-use-full-outer-join-in-mysql/ below:

How to Use Full Outer Join in MySQL

The FULL OUTER JOIN or FULL JOIN in MySQL is a powerful technique to fetch all rows from both tables, including matched and unmatched records. In this article, we will explore how to use FULL OUTER JOIN with practical examples and MySQL queries. Although MySQL doesn't natively support FULL OUTER JOIN, we can achieve the same result using a combination of LEFT JOIN, RIGHT JOIN, and the UNION operator.

The FULL OUTER JOIN in MySQL returns all rows of both tables involved in the JOIN operation in the result set. If there is no match for a particular row based on the specified condition, the result will include NULL values for columns from the table that do not have a match. This makes FULL OUTER JOIN useful when including unmatched rows from both tables.

Note: MySQL does not explicitly support a FULL OUTER JOIN. Instead, we can achieve it by combining a LEFT JOIN, a RIGHT JOIN, and a UNION operator. You can use FULL OUTER JOIN in SQL using the FULL OUTER JOIN keyword.

Syntax:

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

• The LEFT JOIN fetches all rows from the left table and the matched rows from the right table.
  
• The RIGHT JOIN fetches all rows from the right table and the matched rows from the left table.
  
• The UNION operator combines the results of the two SELECT statements.

Let's look at some practical examples for a better understanding of FULL OUTFULL OUTER JOINER JOIN. First, we will create two tables called students and courses on which we will perform the FULL OUTER JOIN.

Creating the Tables:

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(255),
    course_id INT
);
INSERT INTO students (student_id, student_name, course_id)
VALUES
    (1, 'Alice', 101),
    (2, 'Bob', 102),
    (3, 'Charlie', NULL),
    (4, 'David', 103);
CREATE TABLE courses (
    course_id INT AUTO_INCREMENT PRIMARY KEY,
    course_name VARCHAR(50) NOT NULL
);
INSERT INTO courses (course_id, course_name)
VALUES
    (101, 'Mathematics'),
    (102, 'Physics'),
    (103, 'Chemistry'),
    (104, 'Computer Science');

Output:

The students table will look like this:

The courses  table will look like this:

Consider the situation where we want to fetch a list of all students and their respective courses, including those without assigned courses.

Query:

SELECT students.student_id, students.student_name, COALESCE(courses.course_name, 'No Course') 
AS course_name
FROM students
LEFT JOIN courses ON students.course_id = courses.course_id
UNION
SELECT students.student_id, students.student_name, courses.course_name
FROM students
RIGHT JOIN courses ON students.course_id = courses.course_id;

Output:

Explanation: In this result, we see all students and their courses. Students without courses and courses without students are also included.

Suppose We want to see all courses and their enrolled students.

Query:

SELECT courses.course_id, courses.course_name, COALESCE(GROUP_CONCAT(students.student_name), 
'No Students') AS enrolled_students
FROM courses
LEFT JOIN students ON courses.course_id = students.course_id
GROUP BY courses.course_id, courses.course_name
UNION
SELECT courses.course_id, courses.course_name, GROUP_CONCAT(students.student_name) AS enrolled_students
FROM courses
RIGHT JOIN students ON courses.course_id = students.course_id
GROUP BY courses.course_id, courses.course_name;

Output:

Explanation: This result provides a list of all courses and the students enrolled in each course. Courses without students and students not enrolled in any course are also included.

Let's reconsider Example 1 to include an ORDER BY clause and sort the results alphabetically by student name and then by course name.

Query:

SELECT students.student_id, students.student_name, COALESCE(courses.course_name, 'No Course') 
AS course_name
FROM students
LEFT JOIN courses ON students.course_id = courses.course_id
UNION
SELECT students.student_id, students.student_name, courses.course_name
FROM students
RIGHT JOIN courses ON students.course_id = courses.course_id
ORDER BY student_name, course_name;

Output:

Explanation: In this result, the rows are sorted alphabetically by student name and then by course name.

MySQL FULL OUTER JOIN returns data from left and right tables that satisfy the given condition. In MySQL, you can not directly use the FULL OUTER JOIN or FULL JOIN, so we use a combination of LEFT JOIN or RIGHT JOIN and UNION operator.

This tutorial explained how to use these combinations to perform FULL JOIN in MySQL. With the practical example, you can practice FULL OUTER JOIN queries in MySQL and combine records to find insights.

RetroSearch is an open source project built by @garambo | Open a GitHub Issue

Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo

HTML: 3.2 | Encoding: UTF-8 | Version: 0.7.4