Over a million developers have joined DZone.

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

JOINs and sub-queries are similar but different. See what you can do with them and when it's best to use one one over the other.

· Database Zone

Build fast, scale big with MongoDB Atlas, a hosted service for the leading NoSQL database. Try it now! Brought to you in partnership with MongoDB.

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.

Now it's easier than ever to get started with MongoDB, the database that allows startups and enterprises alike to rapidly build planet-scale apps. Introducing MongoDB Atlas, the official hosted service for the database on AWS. Try it now! Brought to you in partnership with MongoDB.


The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}