CODEWITHSUNDEEP
mysqlsql
ViRALiC
ViRALiC

Reputation: 1549

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

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.)

Upvotes: 0

Views: 39

Answers (2)

Esse
Esse

Reputation: 3298

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

Upvotes: 1

meskobalazs
meskobalazs

Reputation: 16041

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.

Upvotes: 1

Related Questions