When doing an INNER JOIN, are columns matched on NULL values?

[jephos249]

Question

In the context of this exercise, when doing an INNER JOIN, are columns matched on NULL values?

Answer

No, when you have NULL values in a column, these are never matched to other NULL values. This is because NULL signifies the absence of any value, and cannot be compared as they will never equal anything. Doing say NULL = NULL results in False.

Let’s take for example the animation given in the exercise, which shows how INNER JOIN works. Let’s say that an additional row was added to each table, with NULL in the C2 column, such that they become

Left table:

C1, C2
A, B
Q, W
X, Y
T, NULL

Right table:

C2, C3
B, C
E, R
Y, Z
NULL, V

If we inner joined these tables the same way, we would end up with the same result, because NULL values are not matched.

C1, C2, C3
A, B, C
X, Y, Z

[joachimrives86292694]

There is no difference between the JOIN and INNER JOIN commands, is there?

[trev1993]

Hey, correct. There is no different.

[designpro91859]

Is it necessary to use ON command whenever we are joining two tables?
And why is the subscription_id column showing twice in the resulsts? Can we do something to make it appear only once, while also joining the two tables ON subscription_id ?

[method2959087669]

The reason why subscription_id is showing twice is that a SELECT * was done, and as subscription_id column exists in both table, one of the columns is for the first table (orders) and the other column is for the 2nd table (subscriptions).
To prevent from having both columns show up, one would have to specify which columns to show. For example: SELECT orders.subscription_id, subscriptions.description FROM orders JOIN ON etc This will only return two columns.

[vinaykararwal4198124]

Basically INNER JOIN works like this
suppose you have two tables TABLE1 and TABLE2.
It select TABLE1 first row and match with TABLE2 rows one by one . If our ON condition is matched it creates new entry .
Then it goes for TABLE2 second row and match with TABLE2 all rows and so on.
if ON condition matches it create new entry otherwise ignore the result .
so we only have matched condtion result .
There all more join statements also like LEFT JOIN , RIGHT JOIN etc.
That’s work on same concept but minor diffrence they don’t ignore the result instead they add NULL in result if ON condition not matched.

[vinaykararwal4198124]

if your ON condition match both tables. then instead of ON you can use USING keyword.

exaple:

SELECT column_list
FROM table_1
JOIN table_2
ON join_condition;

SELECT column_list
FROM table_1
JOIN table_2
USING (column_name); // column name must be same in both tables

[hrithikgajavalli4000]

Thanks for this! It helped me in learning new command!!

[sebastienbaillargeon]

Very useful:
just tested it and helped simplify the code quite a bit.

I went from this:

SELECT 
  orders.subscription_id,
  subscriptions.description
FROM orders
JOIN subscriptions
  ON orders.subscription_id
  = subscriptions.subscription_id
WHERE subscriptions.description = 'Fashion Magazine';

To this:

SELECT
  subscription_id,
  description
FROM orders
JOIN subscriptions
  USING (subscription_id)
WHERE description = 'Fashion Magazine';

Thanks!

[cem.kaya]

Thanks for simplifying. In addition to it, if you type like this:

SELECT * FROM orders

JOIN subscriptions

USING(subscription_id)

WHERE description = 'Fashion Magazine';

It will only print the subscription_id column one time.

[code8202782095]

I really don’t get this. I mean i cannot understand what exactly the Inner Join does . Anyone could explain me please. I would really appreciate that.

[sebastienbaillargeon]

Hey man!
It’s been a while since I’ve done some SQL, but I saved this GIF because I believed it really helps illustrate how Inner Join works:

It really just combines two tables on a matching value and returns the combined result.

They use it a lot to combine different tables on transactional sites, such as a client’s information and their orders being separated into two tables, then performing an Inner Join on both the see the client’s info and their order history by joining on their, let’s say, client ID. This allows to keep data into different files and then joining them when necessary to perform analysis. Let me know if that helps!

[code8202782095]

Thanks man! I appreciate it. It was very helpful

[sebastienbaillargeon]

No problem!
Here, we don’t have much support, so I’m trying to help out others when I can.

Don’t hesitate if anything else comes up.

[tranxuanmanh]

INNER JOIN is a defaul when you just type only JOIN.
there are LEFT JOIN , RIGHT JOIN, FULL JOIN (some database manage system don’t have full join, like mySQL)

[abdouhaji]

VERY USEFUL. thank you so much.

Indeed, The USING clause can be simpler to write when the join condition involves only one column with the same name in both tables.

On the other hand, it would be important to mention that not all DBMS support the USING clause. For instance, SQL Server does not support USING, so you would have to use the ON clause instead.

[abdouhaji]

thank you. very helpful visuals, much appreciated.

[leozeng]

Visual JOIN - I found this is a very useful website with illustrations to help me understand INNER JOIN/LEFT JOIN/RIGHT JOIN/OUTER JOIN. I hope it helps you too.