SQL quiz. If you <b>INNER JOIN</b> <i>tableA</i> (which has

If you INNER JOIN tableA (which has 10 rows) with tableB (which has 5 rows), what is the smallest possible amount of rows that can be returned?

Explanation

0 records may be returned by a query if join condition is never met (if tables do not have common keys over which the join is performed).

Theory

In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents (they can replace each other). In standard SQL, they are not equivalent. INNER JOIN is used with an ON clause, CROSS JOIN is used otherwise.
SQL - INNER JOINS
The most frequently used and important of the joins is the INNER JOIN. They are also referred to as an EQUIJOIN.
The INNER JOIN creates a new result table by combining column values of two tables (table1 and table2) based upon the join-predicate. The query compares each row of table1 with each row of table2 to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row.
Syntax
The basic syntax of INNER JOIN is as follows:
```
SELECT table1.column1, table2.column2...
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
```
Read more: SQL - INNER JOINS

Example - INNER JOINS

(a) CUSTOMERS table is as follows:

| ID | NAME     |
+----+----------+
|  1 | Ramesh   |
|  2 | Khilan   |
|  3 | kaushik  |
|  4 | Chaitali |
|  5 | Hardik   |
|  6 | Komal    |

(b) Another table is ORDERS as follows:

| OID |  ID | AMOUNT |
+-----+-----+--------+
| 102 |   3 |   3000 |
| 100 |   3 |   1500 |
| 101 |   2 |   1560 |
| 103 |   4 |   2060 |

Now, let us join these two tables using INNER JOIN as follows:

SQL> SELECT  ID, NAME, AMOUNT
     FROM CUSTOMERS
     INNER JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

This would produce the following result:

| ID | NAME     | AMOUNT |
+----+----------+--------+
|  3 | kaushik  |   3000 |
|  3 | kaushik  |   1500 |
|  2 | Khilan   |   1560 |
|  4 | Chaitali |   2060 |

Follow CodeGalaxy

Mobile Beta

Send Feedback

SQL - INNER JOINS

Syntax

Example - INNER JOINS

Related Content

Follow CodeGalaxy

Mobile Beta

Keep exploring SQL quizzes

Keep exploring
SQL quizzes