Skip to content
  • YouTube
  • FaceBook
  • Twitter
  • Instagram

Data Analytics Ireland

Data Analytics and Video Tutorials

  • Home
  • Contact
  • About Us
    • Latest
    • Write for us
    • Learn more information about our website
  • Useful Links
  • Glossary
  • All Categories
  • Faq
  • Livestream
  • Toggle search form
  • R Tutorial: How to pass data between functions R Programming
  • how to create and drop a table in SQL SQL
  • How to import data into excel Python Tutorial
  • how to select columns with SQL SQL
  • How to Add Formulas to Excel using Python numpy
  • data cleansing in a business environment Articles
  • How To Fix TypeError: unhashable type ‘slice’ python dictionaries
  • Python Tutorial: Add a column to a data frame Python Dataframe

How To Join Tables In SQL

Posted on February 20, 2021November 8, 2022 By admin No Comments on How To Join Tables In SQL

Estimated reading time: 4 minutes

When running an sql select query, most likely we may have a need to bring in data from other tables.

The reason for this is that tables have a specific purpose for the data they store.

One of the most important features of a data architecture is to avoid duplication.

This has the impact of only storing data on a table that is required.

As a result, joining tables is necessary if you want to get all the information you need .

As an illustration of customer purchase, the purchase details would be on the purchase table.

BUT the customer details would not be stored there. They would be stored on the customer table.

To put it another way, a customer may make many purchases, but you only need their name and address etc. stored once.

The object therefore of joining tables is to use this structure, and avoid duplication over many tables .

For example in the above table, if you had the customer name and address on the purchases table and customer table,

they would always have to be in sync, and it would bring up a maintenance headache for the database administrator.

So what was developed was primary and foreign keys, which help to join tables, based on unique similar values in a column in each table.

What are the types of joins that can be used?

Inner Join: An inner join is where you join two tables but only return the rows that match in both tables.

Left Join: A left join is where you return all the records from the left table, and any matched records from the right table.

Right Join: A right join is where you return all the records from the right table, and any matched records from the left table.

Full Join: A full join uses the left join and right join, and returns all the rows from both tables in the output.

Equally important, where there is no value found a NULL value will be returned.

Lets take some examples to explain the above concepts.

So we have two tables below:

Sales Database, Customer Table

Sales Database, Sales Table

Inner Join

If we run this code, we will return the below. For this reason, the purpose of an Inner Join is to only return matched records common to both.

select a.customer_no, a.customer_type, b.INVOICE_NO 
from dbo.CUSTOMER a -- left table
inner join sales.dbo.sales b -- right table
on a.CUSTOMER_NO = b.CUSTOMER_NO

Left Join

With a left join of the below code will return four rows. All the records from the left table and any matched records from the right table.

select a.customer_no, a.customer_type, b.INVOICE_NO 
from dbo.CUSTOMER a --left table
left join sales.dbo.sales b --right table
on a.CUSTOMER_NO = b.CUSTOMER_NO

Right Join

With a right join of the below code will return three rows. All the records from the right table and any matched records from the left table.

select a.customer_no, a.customer_type, b.INVOICE_NO 
from dbo.CUSTOMER a --left table
right join sales.dbo.sales b -- right table
on a.CUSTOMER_NO = b.CUSTOMER_NO

Full Join

Finally, in order to run a full join, the result of the below code would be as per the below. The objective is to return all rows matching and unmatching.

In the output, there is a Null Value, meaning that that row has no value for INVOICE_NO which is a primary key.

In essence, primary keys are not allowed to have null values.

select a.customer_no, a.customer_type, b.INVOICE_NO from dbo.sales b
full join sales.dbo.customer a
on a.CUSTOMER_NO = b.CUSTOMER_NO
SQL, SQLite Tags:foreign key, full join, inner join, join tables, left join, null in sql, primary key, right join, SQL select

Post navigation

Previous Post: TypeError: ‘str’ object is not callable
Next Post: Welcome to Data Analytics Ireland

Related Posts

  • How to Group By in a Select Statement SQL
  • how to use case statements in SQL SQL
  • select rows with a certain value using SQL SQL
  • how to insert data into a table in SQL SQL
  • Import a CSV file with an SQL query CSV
  • how to select columns with SQL SQL

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Select your language!

  • हिंदी
  • Español
  • Português
  • Français
  • Italiano
  • how to write subqueries in SQL SQL
  • how to compare two lists in Python Python Lists
  • What is data profiling and its benefits? data profiling
  • how to select all records with SQL SQL
  • What is a Primary Key and Foreign Key SQL
  • Python tutorial: Create an input box in Tkinter Python
  • How To Check For Unwanted Characters Using Loops With Python Python Data Cleansing
  • Recursion Definition

Copyright © 2023 Data Analytics Ireland.

Powered by PressBook Premium theme

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Cookie settingsACCEPT
Privacy & Cookies Policy

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these cookies, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may have an effect on your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Non-necessary
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
SAVE & ACCEPT