5 Absolutely Must-Know Differences Between JOINs and Sub-Queries in DB2

JOINs and sub-queries resolve your data retrieval problems when data is located in multiple tables. But whether you use a JOIN or a sub-query is a tricky subject discussed in many forums. The step-by-step points below will help you get a solid footing.

DB2 Joins

1. JOINs are powerful relational operators that combine data from multiple tables into a single result table.

2. The source tables may have little (or even nothing) in common with each other. SQL supports a number of types of JOINs.

3. Any multitable query is a type of JOIN. The source tables are joined in the sense that the result table includes information taken from all the source tables.

4. The simplest JOIN is a two-table SELECT that has no WHERE clause qualifiers. Every row of the first table is joined to every row of the second table. The result table is referred to as the Cartesian product of the two source tables — the direct product of the two sets. (The less fancy name for the same thing is a cross JOIN.) The number of rows in the result table is equal to the number of rows in the first source table multiplied by the number of rows in the second source table.

DB2 Sub-Queries

1. A sub-query is an SQL statement that is embedded within another SQL statement. It’s possible for a sub-query to be embedded within another sub-query, which is in turn embedded within an outermost SQL statement. Theoretically, there is no limit to the number of levels of sub-queries that a SQL statement may include, although any given implementation has a practical limit.

2. A key feature of a sub-query is that the table or tables that it references need not be the same as the table or tables referenced by its enclosing query.

3. Different kinds of nested queries produce different results. Some sub-queries produce a list of values that is then used as input by the enclosing statement. Other sub-queries produce a single value that the enclosing statement then evaluates with a comparison operator. A third kind of sub-query is called a correlated sub-query, which is nested within another query and uses information from it.

Tips to Use in Your SQL Queries:

• When you are dealing with more tables, JOIN is good.
• When you are dealing with fewer tables, sub-queries are good.
• When you need data for the NOT EXISTS and EXISTS conditions, you can use sub-queries.
• You can use both JOINs and sub-queries together in same SQL query.