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 |
    
    Read more: SQL - INNER JOINS

Следи за CodeGalaxy

Мобильное приложение Beta

Get it on Google Play
Обратная Связь
Продолжайте изучать
тесты по SQL
Cosmo
Зарегистрируйся сейчас
или Подпишись на будущие тесты