What will the following query return:
SELECT COUNT (*) FROM table1 INNER JOIN table2 ON table1.id <> table2.id;
a) The number of rows that table1 and table2 do not have in common
b) A list of the rows in common between the two tables
c) The number of rows in table1 times the number of rows in table2 minus the number of rows that the two tables have in common
d) A list of the rows that the two tables do not have in common
e) The number 2
To my surprise, the answer is C. Any good way to explain is welcomed.
First time seeing this too, we can learn from the term “non-equi-join”
Appreciate the links. Will go through them right away.
It would take me too long to write the answer I’d like to give (it would require me to explain joins from scratch which would in turn require me to create diagrams). I could do it, but I would be postponing an answer indefinitely. So here’s what I can give you for now.
Inner joins between two tables are the result of filtering out rows from all the possible combinations of rows from one table with another. (This means that we start from _the number of rows in table 1 times the number of rows in table 2).
The filter criterion is the
ON condition. Since the condition in this example is the ids being different (and assuming that
id is a unique identifier of each row — a reasonable assumption, though it should be explicitly stated), you want to remove the rows in which they are the same.
Thus the correct answer is c).