Join Vs Sub-query Top Differences to Read Today

The differences between joins and sub-queries asked in many interviews and it is tricky subject. The below step by step neatly written points help you grasp the subject quickly.

JOINS

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

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

Any multi-table 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. 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 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.

Sub-Queries

1. A subquery is an SQL statement that is embedded within another SQL statement. It’s possible for a subquery to be embedded within another subquery, which is in turn embedded within an outermost SQL statement. Theoretically, there is no limit to the number of levels of subquery that an SQL statement may include, although any given implementation has a practical limit.
2. A key feature of a subquery 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. Kinds of sub-query -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 subqueries produce a single value that the enclosing statement then evaluates with a comparison operator. A third kind of subquery, called a correlated subquery.

Types of sub queries in SQL

Predicate Subqueries—extended logical constructs in the WHERE and HAVING clause.

Scalar Subqueries—standalone queries that return a single value; they can be used anywhere a scalar value is used.

Table Subqueries—queries nested in the FROM clause.

Tips

• When you are dealing with more tables, then JOIN is good
• When you are dealing with less Tables, then Sub-queries are good. Since tables are more, sub-queries become complex
• When you need data for NOT EXITS and EXITS conditions, then you need to use Sub-queries
• Joins and Sub-queries together you can use in your SQL query