SQL - what's the difference between a JOIN and comparing two rows from different tables?

Question

Warning: This is a pretty rookie soft-question, from someone who has just a few days ago started tinkering with SQL.

So I'm trying to teach myself some basic SQL using SQL Fiddle (http://sqlfiddle.com/). It's just a basic SQL-table maker which allows me to test some queries. You can use it to see my tables quick if you want to.

I'm using MySQL 5.5.32.

Now, my tables look as following:

CREATE TABLE masters (

  name VARCHAR(15),
  num INT,
  riches VARCHAR(15),
  age INT,

  PRIMARY KEY (num)

  );

CREATE TABLE slaves (

  slavename VARCHAR(15),
  num INT,
  slaveage INT,

  FOREIGN KEY (num) REFERENCES masters(num)

  );


INSERT INTO masters
VALUES ("Pharao", 0, "Somewhat", 51);

INSERT INTO masters
VALUES ("Cleo", 10, "Loads", 29);

INSERT INTO masters
VALUES ("Dracula", 15, "Nice estate", 847);


INSERT INTO slaves
VALUES ("LoneSlave", 0, 29);

INSERT INTO slaves
VALUES ("SexyMan", 10, 21);
INSERT INTO slaves
VALUES ("SexyWoman", 10, 19);

INSERT INTO slaves
VALUES ("Zombie", 15, 72);
INSERT INTO slaves
VALUES ("Wolfman", 15, 51);
INSERT INTO slaves
VALUES ("Frankenstein", 15, 51);

Now, I'm just starting to learn about JOIN.

Here's my question; what's the difference between:

SELECT * FROM masters JOIN slaves ON masters.num = slaves.num;

And...

SELECT * FROM masters, slaves WHERE masters.num = slaves.num;

As far as I can tell, these generate the exact same result.

Can someone explain to me what the difference is between these results, if any?

I mean, is it just a preference thing, or is there some clear advantage to one over the other?

(PS: I'd use the soft-question tag, but apparently that's high-treason until I have 1500 rep.)

possible duplicate of [INNER JOIN ON vs WHERE clause](http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause) — Arindam Nayak, Oct 18 '14 at 11:57
possible duplicate: http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins — Esse, Oct 18 '14 at 11:57
have a look on this - http://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause — Arindam Nayak, Oct 18 '14 at 11:57

score 1 · Answer 1 · answered Oct 18 '14 at 11:57

1

The latter one is a shorthand for the first, nothing more. And some may argue, that this is more readable, and more natural to the human mind.

answered Oct 18 '14 at 11:57

meskobalazs

15,741
2
40
63

Good, quick answer. Thanks! – ViRALiC Oct 18 '14 at 12:02

score 1 · Accepted Answer · edited May 23 '17 at 11:49

1

They work the same (even performance is the same).

First one is called "explicit join", second one is called "implicit join". But considering the effect and performance they're same.

More information here: Explicit vs implicit SQL joins

edited May 23 '17 at 11:49

Community

1
1

answered Oct 18 '14 at 11:58

Esse

3,278
2
21
25

Oh, that's perfect. You get best answer in 9 min! – ViRALiC Oct 18 '14 at 11:59

SQL - what's the difference between a JOIN and comparing two rows from different tables?

2 Answers2