Given tableA (containing 10 records) and tableB (containing 5 records), what is the smallest possible number of records that may be returned by a query that performs INNER JOIN of two tables?
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 |
    
    Read more: SQL - INNER JOINS

Follow CodeGalaxy

Mobile Beta

Get it on Google Play
Send Feedback
Keep exploring
SQL quizzes
Cosmo
Sign Up Now
or Subscribe for future quizzes