Toad Data Point Tutorials: SQL Table Joins

Introduction

SQL table joins are common practice in the relational database world. They are a way of eliminating data redundancy and allowing the user of the data to select what they want from the data stores necessary.

SQL allows for quite a number of data stores to be merged into a single SQL statement. The tables are listed in the FROM clause of the SQL, and the WHERE clause is used to match up the columns from two of these tables (at a time) to have data coordinated based on the contents of a column of data.

This blog will focus on these areas of the SQL query language using Traditional Toad Data Point:

• What are SQL Table Joins
• Relationships
• SQL Table Joins Syntax
• Table ANSI Join Syntax
• Different Types of Joins
• TDP Helpful Techniques

I will also illustrate doing similar items using Query Builder (QB). Query Builder allows queries to simply be built, just using a mouse. This feature also has a 'query' tab that allows for the SQL to be reviewed and tweaked. Often, I will use QB to create the base SQL, then I will tweak the SQL it created. You can always make all the changes using just QB though.

In this blog, the TDP Helpful Techniques will be creating the same SQL using Query Builder.

What are SQL Table Joins?

SQL table joins are needed when you want to access data from two or more different data stores in the relational model. These data stores will have to have a column of common data, the join condition, for each pair of tables involved.

In my experience, I see up to a dozen tables being joined together via SQL table joins for various reasons. One of the things you can do in the relational world is keep full descriptions of codes, part numbers, inventory items, etc. in a separate table, and keep just the code for the description with the data. This cuts way down on data redundancy and needlessly storing lengthy descriptions with the rows of data.

<insert part5_1.png> SQL Table Joins – EMP and DEP Join Condition

SQL table joins allow for these descriptions to easily be pulled into the result set when needed. This example is showing Query Builder and an Oracle RDBMS where there are pre-programmed relationships between the various tables.

In this example, the Join condition on the DEPTNO columns is automatically painted in with an equality type of join (equal condition…called an Equi-Join).

<insert part5_2.png> EMP and DEPT Join Condition ANSI Syntax

This illustration shows the SQL joining the EMP and the DEPT tables together using the DEPTNO column. This is the ANSI (American National Standards Institute) SQL. ANSI SQL is commonly used and is a requirement for using any ODBC driver access. ANSI SQL is the common SQL across the relational database industry.

Relationships

Most applications have the table relationships coded into the table data stores themselves. This is called Referential Integrity, Parent/Child Relationships, or Primary/Foreign Key Relationships. These data rules are coded to aid the end user with the required data joins but also to ensure that the data on these key values (the DEPTNO in this example code) always has a match. The business analyst, the power user or the application user does not need to be concerned that there are employees not assigned to a department. With these rules in place, a row that violates this condition (ie: trying to insert a new employee with a DEPTNO that is not in the DEPT table) would not be inserted. The data in the tables are protected from errors in this manner.

<insert part5_3.png> EMP and DEP Join Condition in Table Viewer

With Toad Data Point, simply double click on a table name and the Table Viewer shown below will come up. Notice the tabs. You can see the columns as defined in the table and their data types/default values. I always like seeing some of the actual data in the Data tab. This Oracle RDBMS view also shows the Relationships and Constraints.This is a view of the Relationships tab, showing the DEPT table. You can see the DEPT table along the top (I used the connection screen to make these tabs appear in different colors) and that the DEPTNO is the primary key (a gold key). There is a coded relationship to the EMP table.

<insert part5_4.png> DEPT Table Primary Key Syntax

This illustration shows the actual syntax used to insure this DEPTNO relationship. This code states that the DEPTNO is the primary key, and primary keys can only have 1 value per table. The fact that this is a primary key will insure there are no duplicate values in this table.

<insert part5_5.png> EMP Table Foreign Key referencing DEPT Table DEPTNO

This syntax is then included in the EMP table to team up the DEPTNO column here with that of the DEPTNO in the DEPT table. This code will ensure any row being inserted or changed has a corresponding DEPTNO value in the DEPT table. With this code in place, one cannot change the EMP DEPTNO column to a value that does not exist in the DEPT table, nor can a row be inserted where the value of DEPTNO is not in the DEPT table.

This type of coded relationship is both easy for the programming staff setting up the applications and makes life super easy for the application code because now there doesn’t have to be any syntax checking. The users of the data also benefit because when using tools like Toad and Toad Data Point, the relationships are picked up and included in their selection of these tables.

SQL Table Joins Syntax

<insert part5_6.png> EMP and DEPT Join Syntax

This SQL shows the ‘old school’ Oracle syntax. I have worked with the Oracle database from almost day one and the ANSI standard didn’t exist back then. So, I got comfortable with what I call the standard syntax. Toad and Toad Data Point both support the standard database syntax (in this case, the Oracle RDBMS) and the ANSI standard SQL.

Table ANSI Join Syntax

<insert part5_7.png> LIKE Ends with S

This is the same SQL but in ANSI format. I like the ANSI format as it clearly defines the join condition in simple terms.

Different Types of Joins

There are many types of join conditions for the relational model. We have been reviewing the Equi-Join, or an equal condition between two tables.

Non-equi joins have the join columns that are not an equal condition, such as Not Equal, Less than, Greater than and Between.

Cartesian Joins (no join condition).

Outer Joins shows all rows from a table whether there is a match or not.

<insert part5_8.png> Standard Oracle Outer Join Syntax

This example will return all the rows from the DEPT table, whether there is a match in the EMP table or not. I don’t like this standard syntax because it’s difficult to remember which side to put the ‘+’ sign.

<insert part5_9.png> Outer Join Data

Notice here that the last row, the Operations department, has no employees but still appears in the list.

<insert part5_10.png> Outer Join ANSI Syntax

This syntax shows the ANSI outer join condition. To me, this clearly spells out which side will have all the rows, whether there is a match or not.

TDP Helpful Techniques

The Query Builder’s canvas also automatically paints in the join conditions.

<insert part5_11.png> Query Builder with EMP and DEPT Tables

<insert part5_12.png> Query Builder Join Control

This panel is accessed by right-clicking on the join condition and picking properties from the list that appears. From here, you can change the Equi Join to any type of join you need. Notice in the lower left is the outer join conditions in the form of a check box.

<insert part5_14.png> EMP and DEP Join Condition

This is the syntax created by the Query Builder with the ‘Select all rows from DEPT’ checked.

Related Links

Blog: How to use Toad Data Point to solve data preparation issues 

Blog: What is data preparation and why is it key to the business analyst? 

Blog: Data preparation and analysis: Using data efficiently in the era of dark data 

Blog: How to use Quest® Toad® Data Point to leverage data automation 

Blog: How to use Quest® Toad® Data Point to arrange and pivot data to spot data patterns and identify problems 

Blog: Three data preparation challenges that Quest® Toad® Data Point can solve 

Have questions, comments? 

Head over to the Toad for Oracle forum on Toad World®!  Chat with Toad developers, and lots of experienced users. 

Help your colleagues

If you think your colleagues would benefit from this blog, share it now on social media with the buttons located at the top of this blog post.