Tuesday, May 17, 2022
Latest:

Data Analytics Ireland

Data Analytics and Video Tutorials

foreign key

You can use a foreign key on a column to join another database table who has a primary key of the same values.

SQL SQLite 

how to join tables in SQL

admin 2526 Views 0 Comments , , , , , , ,

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 in 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 on a 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 helps 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 

What is a Primary Key and Foreign Key

admin 2300 Views 0 Comments , , , ,

When working with databases in your data analytics projects, most likely you will deal with more than one table. As a result in certain scenarios there will be a link between the tables

This will ensure that you can use data from the two or more tables that can be joined together. But how would you do that?

In order to join two tables, you will need columns in both tables that have values that are unique and of similar values.

In other words you need to create what is called a primary key, its characteristics are as follows:

  • Its values are unique.
  • They cannot be null.

So where does a foreign key come in? Say we have two tables Table A and Table B.

Table “Customer Table” will have a column that has a primary key call Customer_No

Table “Sales Table” also has a column with a primary key called Sales_No.

  • Now we cant join the two tables, because the primary keys are different, and will have different values
  • But in the Sales Table, we will have an additional column called Customer_No, and it will hold some of the unique values that are held in the Customer Table, Customer_No column.
  • This Customer_No in the Sales Table is called the foreign key. It is unique, has null values and can be connected to a primary key column Customer_No on the Customer Table.

In this way tables can be connected where values in columns are not always the primary key of a table.

So let’s look at tables in SQLite and see how this works in practice.

Below you have a Customer Table and the Sales Table

In both tables, we have a primary key, though they are not the same primary key. As a result, you would not be able to join the tables as the primary keys contained in them do not have the same values, and this is how you would normally join.

In saying that, the two tables can be joined as the foreign key of Sales is related to the primary key of Customer. If invoice_no was not on Sales , then you could make the customer_no in sales the primary key.

So lets look at the tables below with data in them.

Normally when you try to join two tables, it is on the primary key. On the below invoice_no is the primary key of the table Sales, and customer_no is the primary key of the customer table.

While their values in each column are unique, they are not the same , so a join would fail as per the below:

But if you change on the join b.invoice_no TO b.customer_no, it will now bring back all the values asked for. The reason?

The primary key on one is linked to the foreign key on the other, with similar unique values.

As a result, you can still have a primary key column, not in another table and join to that table as long as there is a foreign key column with the same values.

This helps to maintain the table structure, without having to remove primary keys.