SQL FULL (OUTER) JOIN

The SQL FULL OUTER JOIN keyword returns all records from the left side table (tableA) and from the right side table (tableB).

The SQL FULL OUTER JOIN keyword combines the result of both LEFT JOIN and RIGHT JOIN query result.

The SQL complete join is the outcome of a left and right outer join, and the join tables contain all of the records from both tables. When no similarities are discovered, it returns NULL.

All records from both tables will be combined into one connected table, with NULLs filled in for missing matches on either side.

SQL join and SQL complete outer join are the same thing. It's commonly referred to as a SQL FULL JOIN.

Syntax :

SELECT columns
FROM table1 name
FULL JOIN table2 name
ON table1.coumn_x = table2.column_y;

where SELECT, FULL JOIN, and ON are the keywords, columns are the list of columns, table1 is the first table, table2 is the second table, and column_x and column_y are the columns for performing Left Join, followed by a semicolon.

Example 1: Let’s apply Full Join to two tables, the employee table, and the department table :

Select employee.e_name, employee.e_dept, department.d_name, department.d_location from employee
FULL JOIN department
ON employee.e_dept = department.d_name;

Example 2: To understand this clearly, let's look at the following employees and departments tables.

Table: employees

Table: departments

Let's imagine you only want to get the names of all the employees and available departments, irrespective of whether or not they have equivalent rows in the other table. In that situation, you can use a full join, as seen below.

By linking the employees and departments tables together using the common dept id field, the following statement obtains all departments, including the data of all employees.

SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 FULL JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_name;

Example 3: Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS Table is as follows.

Now, let us join these two tables using FULL JOIN as follows.

SELECT  ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output:

There is no FULL JOIN matching in MySQL. However, using an LEFT JOIN, RIGHT JOIN, and the UNION verb, you may simulate one. In the given description, we'll return all columns for simplicity's sake, but you'll still want to specify particular columns in both SELECT queries.

Example 1: The first step is to examine the schema and choose which columns to display. We'll need three columns to display students with their courses: student.first_name, student.last_name, and course.name.

Students were linked to the course IDs they were taking. All we need to do now is add the course details. The course_id column in the student_course table is known. It must be joined with the course table's id column. This is the query that results:

SELECT student.first_name,
student.last_name,
course.name
FROM student
JOIN student_course
  ON student.id = student_course.student_id
JOIN course
  ON course.id = student_course.course_id;

Example 2: The following Sql Full Outer Join Select all columns query will display all the columns and rows present in Employees and Department.

SELECT *
FROM [Employee]
 FULL OUTER JOIN
     [Department] ON
 [Employee].[DepartID] = [Department].[id]SQL - Full Join in MySQL using Left Join, Right Join, and Union Keyword

SELECT * FROM products
LEFT JOIN inventory ON products.id = inventory.product_id
 
UNION ALL

SELECT * FROM products
RIGHT JOIN inventory ON products.id = inventory.product_id
WHERE products.id IS NULL

Example 1: Two tables tih_Courses and Registration as shown below:

tih_Courses

Registration

To Select all the course name and registered student to the respective courses.

SELECT tih_Courses.Course_Name, Registration.Registered_Students 
FROM tih_Courses 
FULL JOIN Registration 
ON tih_Courses.Course_ID = Registration.Course_ID;

Example 2: use the following Employee and Department tables in all examples.

Employee Table

Department Table

Now, look at the following FULL JOIN query.

SELECT emp.EmpId, 
emp.FirstName, dept.DeptId, dept.Name 
FROM Employee emp
FULL JOIN Department dept
ON emp.DeptId = dept.DeptId;

Example 3: To retrieves all the departments as well as the details of all the employees by joining the employees and departments tables together using the common dept_id field.

SELECT t1.emp_id, t1.emp_name, t1.hire_date, t2.dept_name
FROM employees AS t1 FULL JOIN departments AS t2
ON t1.dept_id = t2.dept_id ORDER BY emp_name;

Example 4: Run through that example of a FULL OUTER JOIN that shows both the EmployeeName and ProjectName again. Here's the query:

SELECT Employees.EmployeeName, 
Projects.ProjectName
FROM Employees
FULL OUTER JOIN Projects
ON Employees.ProjectID=Projects.ProjectID
ORDER BY EmployeeID;

When you use the RIGHT JOIN keyword with a select statement, it creates a new result table by joining the three tables previously with all of the records from the right and left tables.

Syntax :

You can join 3 tables using the following INNER JOIN syntax :

SELECT table1.column1_name, table1.column2_name,..., 
table2.column1_name, table2.column2_name,..., 
table3.column1_name, table3.column2_name,..., 
FROM table1
FULL JOIN table2 ON table1.table1_id = table2.table2_id 
FULL JOIN table3 ON table2.table2_id  = table3.table2_id;

Query :

SELECT students.full_name, students.gender, 
contacts.mobile_number, contacts.email_address, universities.university_name
FROM universities
FULL JOIN students ON universities.university_id = students.university_id
FULL JOIN contacts ON students.student_id = contacts.student_id;

Output:

The Full Join returns all records from both the left and right tables. It is generally recommended to join two tables.

Let's imagine we have two tables, Table A and Table B, for instance. When we use Full Join on these two tables, we get all of the records from Table A and Table B. The null value is displayed instead if the criterion is not satisfied.

The full join reveals all of the records in Tables 1 and 2.

Syntax:

The syntax of the Full Join in SQL Server is as shown below:

SELECT Table1.Column(s), Table2.Column(s),
FROM Table1
FULL OUTER JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column

--OR We can Simply Write it as

SELECT Table1. Column(s), Table2. Column(s),
FROM Table1
FULL JOIN
Table2 ON
Table1.Common_Column = Table2.Common_Column

We'll utilise two tables from our database (Employee and Department) for this SQL Full Join example.

where the keywords are SELECT, FULL JOIN, and ON, columns are a list of columns, table1 is the first table, table2 is the second table, and column_x and column_y are the columns for conducting Left Join, followed by a semicolon.

Example 1: Find the names of customers whose resident cities are not present in the cities table.

SELECT t1.Customer, t1.City, t2.city_name
FROM customers as t1 FULL JOIN cities as t2
ON t1.City = t2.city_name;

Cities like as Birmingham, Michigan, Canberra, Budapest, and Amsterdam are not exist in the cities table in the example above, yet they are nevertheless displayed on the FULL join with NULL values. Cities like Ontario, Pune, and Washington, D.C., which are not present in customers but are in the cities table, are likewise displayed with NULL values for not matching entries.

Example 2: Find the names of customers whose resident cities are not present in the cities table.

SELECT t1.Customer, t1.City, t2.city_name
FROM customers as t1 FULL JOIN cities as t2
ON t1.City = t2.city_name;

Cities like Birmingham, Michigan, Canberra, Budapest, and Amsterdam are not existent in the cities table in the preceding example, yet they are still presented on the FULL join with NULL values. Likewise, cities such as Ontario, Pune, and Washington, D.C., which are not contained in customers but are in the cities table, are displayed with NULL values for not matching entries.

Main Article :- Sql difference between FULL JOIN and CROSS JOIN

A full outer join is made up of both the left and right outer joins. It returns all rows in both tables that fit the where clause of the query, and it populates the unpopulated columns with null values if the where clause requirement for those rows cannot be met.

When there is no straightforward INNER JOIN option, a CROSS JOIN is used.

You might be shocked to learn that CROSS JOIN is really efficient.

If an INNER JOIN statement becomes too complicated and slow, it may be time to switch to a CROSS JOIN statement.

Cross-join statements are commonly used in reporting.

A cross join returns all unique combinations of all rows by creating a cartesian product between the two tables. Because you're merely joining everything to everything, there's no on-going clause.

In reports, CROSS JOIN commands are used to retrieve averages and predictions. They're most commonly used to get total sums for each client or order (for instance) based on the number of records in a database.

Syntax full join:

SELECT columns
FROM table1 name
FULL JOIN table2 name
ON table1.coumn_x = table2.column_y;

Syntax cross join:

SELECT * FROM t1
CROSS JOIN t2
WHERE t1.id = t2.id;

All rows from both tables (left and right tables), even non-matching rows from both tables, are returned by a Full Join or Full Outer Join.

FULL OUTER JOIN or FULL JOIN Produce all rows from both the left and right sides of the join at all times.

Example: Write a query to retrieve Name, Gender, Country, Salary and DeptName from tbl_Employee and tbl_Department table.

FULL JOIN or FULL OUTER JOIN Query

SELECT Name, Gender,country, Salary, DeptName   
FROM tbl_Employee  
FULL OUTER JOIN tbl_Department  
ON tbl_Employee.DepartmentId = tbl_Department.DeptId  
OR
SELECT Name, Gender,country, Salary, DeptName   
FROM tbl_Employee  
FULL JOIN tbl_Department  
ON tbl_Employee.DepartmentId = tbl_Department.DeptId;

Note: You have the option of using FULL JOIN or FULL OUTER JOIN. The keyword OUTER is optional.

INNER JOIN

Inner join only returns rows that check in both tables; non-matching rows are removed.

Depending on the join-predicate, the INNER JOIN produces a new result table by integrating column values from two tables (table1 and table2).

FULL JOIN

Full Outer Join returns all rows from both tables (left and right), including non-matching data from both tables.

The combined table will contain all of the records from both tables, as well as NULLs for any missing matches along either side.

Both union and join can be used to merge data from one or more tables into a single result. They approach it in various ways.

Full Join

Full joins combine data into new columns in SQL. When two tables are combined, the data from the first table is displayed in one set of columns beside the data from the second dataset in the same row.

Because it combines the tables, SQL full join is slower than SQL union since it checks each and every column.

The join clause is only usable when the two tables being used have at least one column in common.

Union

Data is merged into new rows using unions. there will be two select statements that will be utilised to union data from the table.

Union is faster since it just passes the first SELECT command, parses the second, and adds the results to the end of the output.

Example 1: One method to simulate a full join is to take the union of two outer joins:

select * from apples as a
left outer join oranges as o on a.price = o.price
union
select * from apples as a
right outer join oranges as o on a.price = o.price

Only when the number of columns and matching attributes have the same scope does the Union clause apply.

In this situation, this yields the intended outcomes, but it isn't appropriate in all cases. Assume the tables contain duplicate records (remove the primary key and insert twice to create this situation). A full join does not eliminate duplicates, but a UNION does. UNION ALL is also not the best option because it would result in bogus duplication. In fact, because UNION generates two independent result sets before combining them, there is no way to avoid this, as the two result sets must "know about each other" in order to provide the correct results.

In some circumstances, duplicate findings are expected and correct. Selecting only particular columns in which there are duplicates, for example, could result in this scenario even though the rows are unique. This isn't true in relational theory because a set can never have duplicates, but it is true in SQL.

Example 2: FULL JOIN (MySQL does not support FULL JOIN), then you can use UNION ALL clause to combine these two JOINS as shown below.

SELECT  ID, NAME, AMOUNT, DATE
 FROM CUSTOMERS
 LEFT JOIN ORDERS
 ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION ALL
 SELECT  ID, NAME, AMOUNT, DATE
 FROM CUSTOMERS
 RIGHT JOIN ORDERS
 ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

The combined table will contain all of the records from both tables, as well as NULLs for any missing matches on either side.

A FULL OUTER JOIN with null is a standard name for a FULL join.

SELECT table1.column1, table2.column2, … 
FROM table1 
FULL JOIN table2 ON table1.commonColumn = table2.commonColumn

Notes:

Table2 is the FULL table in this syntax, while table1 is the left.

If the commonColumn values do not match, table1.column1 returns NULL.

If the commonColumn values do not match, table2.column2 returns NULL.

Example 1: Depending on your needs, the given condition could be any given phrase.

Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

Table 2 − ORDERS Table is as follows.

Now, let us join these two tables using FULL JOIN as follows.

SELECT  ID, NAME, AMOUNT, DATE
FROM CUSTOMERS
FULL JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

Output:

A clause of the SELECT statement is the FULL OUTER JOIN. The FULL OUTER JOIN in SQL integrates the results of both left and right outer joins and provides all (matched or unmatched) rows from both ends of the join clause.

All rows from both tables are used in a SQL Full Outer Join. If there are any unmatched rows, NULL values are displayed.

Syntax:

SELECT * 
FROM table1 
FULL OUTER JOIN table2 
ON table1.column_name=table2.column_name;

Example 1: The full outer join:

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

Table1: First Table in Database.

Table2: Second Table in Database.

column_match: The column common to both the tables.

Output :

Example 2: use the FULL OUTER JOIN to query data from projects and members tables:

SELECT m.name member, 
p.title project
FROM 
pm.members m
FULL OUTER JOIN pm.projects p 
ON p.id = m.project_id;

Example 3:

SELECT * FROM table_A 
FULL OUTER JOIN table_B 
ON table_A.A=table_B.A;

Because this is a full join, the output includes all rows (both matching and nonmatching) from both tables. Because table table A and table table B only have one match, just one row of output shows values in all columns. The remaining rows of output contain just values from table table_A or table table_B, with missing values in the remaining columns.

POs and SOs will be paired depending on their sequence, but FULL OUTER JOIN will pair them in all feasible permutations.

You must first perform a ROW NUMBER() to preserve the order.

SELECT COALESCE(SO2.company, PO2.company) AS Company,
COALESCE(SO2.part, PO2.part)       AS Part,
so.salesorder,
po.purchaseorder
FROM (SELECT *,
Row_number()
OVER (
partition BY so.part, so.company
ORDER BY so.salesorder) AS SO_Sequence
FROM   so) AS SO2
FULL OUTER JOIN (SELECT *,
Row_number()
OVER (
partition BY po.part, po.company
ORDER BY po.purchaseorder) AS PO_Sequence
FROM   po) AS PO2
ON SO2.company = PO2.company
AND SO2.part = PO2.part
AND SO2.so_sequence = PO2.po_sequence;

Example: how can remove duplicate rows in below procedure in TBLTicketAnswers i have some record with same TicketID and i don't want to display just one row:

SELECT TBLTickets.TicketID ,
TBLTickets.UserID ,
TBLTickets.AttachFile ,
TBLTickets.HasFile ,
TBLTickets.Title ,
TBLTickets.Question ,
TBLTickets.Flag ,
TBLTickets.InsertDate ,
TBLTicketBranchs.BranchName ,
TBLTicketAnswers.AnswerID ,
TBLTicketAnswers.Answer ,
TBLUsers.UserName ,
TBLUsers.UserOwner ,
TBLUsers.Corporation
FROM    dbo.TBLTicketBranchs
INNER JOIN dbo.TBLTickets ON dbo.TBLTicketBranchs.BranchID = dbo.TBLTickets.BranchID
INNER JOIN dbo.TBLUsers ON dbo.TBLTickets.UserID = dbo.TBLUsers.UserID
LEFT OUTER JOIN dbo.TBLTicketAnswers ON dbo.TBLTickets.TicketID = dbo.TBLTicketAnswers.TicketID
WHERE   ( TBLTicketBranchs.ResellerID = @ResellerID   --without flag (@flag=0)
AND ( TBLTicketAnswers.Answer LIKE N'%'
+ @Keyword + '%'
OR @Keyword IS NULL
OR TBLTickets.Title LIKE N'%' + @Keyword
+ '%'
OR @Keyword IS NULL
OR TBLTickets.Question LIKE N'%'
+ @Keyword + '%'
OR @Keyword IS NULL
OR TBLUsers.UserName LIKE N'%' + @Keyword
+ '%'
OR @Keyword IS NULL
)
)

Output: