SQL: Left & Right 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

Left and right joins in SQL can be used to combine data from at least two tables found within a relational database. SQL is the database language that indicates how left and right joins are to be executed. In this lesson, you'll learn how to carry out left and right joins with SQL.

SQL and the Relational Database

One very important feature of SQL is that it deals with relational databases. A SQL JOIN allows data from two tables to be combined. In this lesson, we will review LEFT and RIGHT joins. First, we will review the logic behind a JOIN clause in SQL.

Let's say that you have a table called customers which contains six columns related to each customer including first and last names, address, phone number, date of birth, and customer id number. And, you also have a second table called orders which contains 3 columns including customer id number, order date, and order number. Consider that you wish to offer customers a promotion on their birthday, but you only want to include customers who have made a purchase within the past year. It would be rather tedious to look at a screen containing both tables side by side trying to figure out which customers placed an order within the last year and compare their customer id number to their birthday.

A solution to finding customers who placed in order within the last year and locating their corresponding birthday is with a JOIN query in SQL. A join query could be created that returns a table listing the customer's first and last name, their birthday, and their address, but only for those customers who placed an order within the last year. This can be verified by the customer id, which is the same for both tables as it is considered the unique identifier. With the new table, special birthday promotions can be mailed out to customers. This is an example of the value of a relational database as well as the join query in SQL.

A relational database is made up of tables that can be related to one another. The tables can be related by the data found within them. For example, a relational database may contain two tables such as:

Students

last_name first_name ID
Doe John 012
Smith Joe 015
Brown Jane 018
Johnson Jim 210

Courses

Instructor ID course_name course_location
Jones 012 Biology Main
Jones 018 Chemistry Online
Miller 015 English Main
Miller 210 Writing Online

Notice that the two tables above are related by the ID column. The first table consists of the column names referred to as attributes: last_name, first_name, and ID. The second table consists of attributes: instructor and ID. The rows found under attributes are known as tuples. The tuples consist of data, which in the case of a relational database, are related. The relation in the two tables shows that the instructor Jones is assigned students John Doe for Biology and Jane Brown for Chemistry and the instructor Miller is assigned to students Joe Smith for English and Jim Johnson for Writing.

Joins

A SQL join is used to combine data from two or more tables. Various types of joins can be carried out, all of which can be done based on the premise that data found within the tuples or rows can be matched. The structure of a SQL join and a brief description of the meaning is found below:

  • LEFT: LEFT indicates the second table to be joined.
  • ON: ON describes how the two tables are joined using the same attribute name.

The following examples of left and right joins will make clear how the syntax works.

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