Copyright

SQL: Full Outer Joins

Instructor: Katherine Wenger

Kate has a Bachelors, Masters, and is a Ph.D. candidate in the fields of Information Technology and loves teaching students

The full outer join, or the full join, is the SQL syntax used to combine all the rows from two or more tables. With the full outer join, no rows will be left out of the resulting table from the query. This lesson provides examples and explanations for using a full outer join.

Introduction

A join query in SQL is used to combine rows from two or more tables. There are a number of different types of SQL joins, including inner, left, right, and full joins. The type of join you select depends upon the common field(s) among the tables that you wish to combine. A full outer join returns all rows from both tables. An advantage of using a full outer join to return all rows from both tables is that the combined table will include rows with null data, so nothing is left out. This will become apparent with an example. We will use the following two tables to elaborate upon the full outer join query:

Student

last_name first_name id major
Doe John 012 Biology
Smith Joe 015 English
Brown Jane 018 Biology
Johnson Jim 210 English
James Peter 202 Biology
Jones Paul 203 English

Registration

instructor course_name course_location id
Jones Biology101 Main 012
Jones Biology101 Main 202
Jones Chemistry101 Online 018
Miller English101 Main 015
Miller Writing101 Online 210

The student table includes columns for a student's last name, first name, the student's ID (which is a unique identifier), and the student's major. The registration table indicates instructors with assigned courses, course locations, and assigned students as indicated by the student ID.

Full Outer Join Syntax

The SQL syntax for the full outer join is as follows:

SELECT table.column-names
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;

Now, let's do a full outer join on the two tables using the following SQL statement:

SELECT student.id, student.major, registration.course_name
FROM student
FULL OUTER JOIN registration
ON student.id = registration.id;

The following table would be the result of the join:

id major course_name
012 biology biology101
015 English English101
018 biology chemistry101
210 English writing101
202 biology biology101
203 English

The resulting table from the full outer join shows that Paul Jones has not yet registered for any courses. This is valuable information for the registration department because they can contact Paul Jones to remind him to register for courses.

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support