Advanced Table Joins and Data Sorting with Toad Data Point

Introduction

Table joins are a helpful function to get familiar with in Toad Data Point. What happens if you need to relate two different rows within the same table? In the EMP table, we have employees who are also managers, so how do we get a list of the employees and who they report to? The secret here is the MGR column (manager number) is actually an EMPNO of the managing employee.

A self join is where you list the same table two times in the FROM clause. Table Alias (renames the table for purposes of the query, I also use this technique to be self-documenting and to fully-qualify column names) is the answer here.

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

Introduction to the Self Join
Self-Join Syntax
ANSI Self-Join Syntax
Order By Clause
TDP Helpful Techniques

Introduction to the Self Join

Occasionally you might need data from two different rows in the same table at the same time.

SQL is magical this way in that you can indeed list the same table twice, or as many times as necessary really, because of the ability to use table aliases to rename the table for the purposes of the query.

table joins 1

<insert part6_1.png> EMP Self Table Join

Table aliases are the secret here. A table alias renames the table for the purposes of the query. I usually use this technique to shorten a long table name to some short acronym as qualifying all of the columns back to a table name makes the query syntax both self-documenting and is a good coding practice.

Qualification is necessary when you are joining two or more tables that have the same column name, such as the EMP and DEPT tables. The DEPTNO is the common field between these two tables and when DEPTNO is being referenced, even if just in the WHERE clause for join purposes, the query still needs to know which table the DEPTNO being referenced belongs to.

When you join a table to itself, so you can match up two different rows in the same table, all of the columns in the tables are the same, it’s the same table. So, you have to qualify everything.

In the above example, I like to use table aliases to describe the row I’m interested in from each table: EMPLOYEE data from the one, and the related but separate row of the MANAGER data. I qualified the columns in the SELECT clause as I wanted the EMPLOYEE data and who that employee reports to. The MGR number of the EMP table contains the EMPNO of the manager they report to. This is the self-join condition.

Notice that the correct data was indeed returned.

Self-Join Syntax

table joins 2

<insert part6_2.png> EMP Table Join – Self-Join Syntax

In this example, I illustrate using column aliases to give the column data descriptive information. Now, not only does the data make more sense, everything is fully qualified and named accordingly. I am using ‘old school’ SQL syntax to join the two versions of the EMP table. My SQL skills pre-date the ANSI standard and I’m still used to coding SQL this way.

Self-Join ANSI Syntax

table joins 3

<insert part6_3.png> EMP Self-Join ANSI Syntax

This illustration shows the same SQL but using ANSI syntax.

Order By Clause

The data is stored in tables pretty much in the order that it was originally inserted. Some databases can relocate the row during an update operation, if it no longer fits in the original space allocated. Different databases handle this situation differently and different databases can reuse space opened up by a deleted row as well.

There is no guarantee about the order of the rows being returned by the SQL unless you do a sort, using the ORDER BY clause.

table joins 4

<insert part6_4.png> Sorting the Data – Advanced Table Joins and Data Sorting with Toad Data Point

This illustration shows the sorting of the column DEPTNO first in ascending order. Ascending is the default behavior. Then within this sort, the data is sorted by SALARY descending. This ORDER BY clause will put the data in DEPTNO order first, then the top salaries first (top then the others in descending order) within each department.

Some databases allow for some additional syntax such as ‘NULLS FIRST” or ‘NULLS LAST’.

This is the syntax I have used through the years. I do not believe there is a limit to the number of columns you can sort on in a single query.

table joins 5

<insert part6_5.png> Standard Oracle Outer Join Syntax

This illustration shows that you can also name the sorting columns by their location in the SELECT clause. However, I rarely see this type of syntax because this simply isn’t a good coding practice. If someone comes along and adds a column to the beginning of the SELECT line, the sort does not sort on the correct columns anymore.

The prior Order By example works every time.

Toad Data Point Helpful Techniques

Query Builder actually makes the self-join easy. People struggle with the self-join concept and syntax but Query Builder gives a nice visual of data if desired.

table joins 6

<insert part6_6.png> Query Builder with an EMP Table Self-Join

Notice the above illustration that you simply drag and drop the EMP table onto the canvas twice. Double clicking on the header of the table on the canvas allows for a Table Alias to be entered. I renamed the tables according to the data that I desire from each. I also used the ‘Field Alias’ area to rename the columns to a more descriptive column name.

Query Builder makes it easy to now select the EMPLOYEE data from one version of the EMP table and the MANAGER data from the second version of the EMP table.

The join is built by dragging the MGR number from the EMPLOYEE EMP table and dropping it on the EMPNO of the MANAGER EMP table.

Also notice I put the sort in from the prior ORDER BY example as well. In Query Builder, you select the columns to sort in the order in which you wish to sort them. In this case, DEPTNO ascending and SAL descending.

table joins 7

<insert part6_7.png> Query Builder Self-Join Syntax

This illustration shows the Query tab of Query Builder. Notice Query Builder uses ANSI syntax by default.

table joins 8

<insert part6_8.png> EMP Self-Join Data

table joins 9

<insert part6_9.png> Employee Data Outer Join

Just to show how easy Query Builder is to use, I’m including an outer join as I’d like to see ALL of the Employee data including those (the PRESIDENT) that do not report to anyone else. Click on the join condition between the two tables to get this panel. Notice this panel picked up our table alias names so this too is easy to select which version of the EMP table you wish to see all of the data.

table joins 10

<insert part6_10.png> EMP Table Self-Join/Outer-Join Syntax

Try Toad free for 30 days.

Free 30-day Trial

Already in a trial? Talk to sales or buy now online.

Already a loyal fan of Toad for Oracle? Renew now.

Again, Query Builder to the rescue. Our SQL is becoming suddenly much more complex, but it was easy for us to do with just the click of a mouse. Again, the ANSI syntax clearly defines which version of the EMP data will return all of the rows, whether there is a match or not (Outer-Join).

table joins 11

<insert part6_11.png> EMP

Notice in this example that the PRESIDENT row now appears as this person doesn’t report to anyone, no MGR number. The sort continues to work, putting this large salary first.

Unit Summary

This post illustrated table joins; how to join two different rows together from the same table. This unit also introduced the Order By clause. I also illustrated how well Query Builder works to build a rather complex query easily.

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.

What’s Next?

Explore and Select a Toad product at https://support.quest.com/download-product-select
Toad Edge download Free Trial Version from https://www.quest.com/register/111545/
Buy Online or Request Pricing for Toad Edge at https://shop.quest.com/682/purl-toad-edge-selection
Get Product Support for Toad Edge at https://support.quest.com/
Have questions about Toad Developer Tools? Click Start Discussionand this blog topic will be transferred to the Toad World Forums.