CODEWITHSUNDEEP
sqljoinkey
Virus721
Virus721

Reputation: 8335

In which order should I join my two tables?

I have the following tables :

CREATE TABLE IF NOT EXISTS "image_e"
(
    "id"    int unsigned NOT NULL AUTO_INCREMENT,
    "title" varchar(32),
    "alt"   varchar(16)  NOT NULL,

    PRIMARY KEY ("id")

)   ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_bin;

CREATE TABLE IF NOT EXISTS "image_version_e"
(
    "id"      int unsigned      NOT NULL AUTO_INCREMENT,
    "imageId" int unsigned      NOT NULL,
    "format"  varchar(4)        NOT NULL,
    "size"    int unsigned      NOT NULL,
    "width"   smallint unsigned NOT NULL,
    "height"  smallint unsigned NOT NULL,
    "src"     varchar(64)       NOT NULL,

    PRIMARY KEY ("id")

)   ENGINE = MyISAM DEFAULT CHARSET = utf8 COLLATE = utf8_bin;

ALTER TABLE "image_version_e"
    ADD UNIQUE KEY ("imageId", "format", "width", "height"),
    ADD UNIQUE KEY ("src");

I would like to join these to table to link the image row with it's associated image version row(s). Does it really matter in which order I join them ? What is the difference if I do:

SELECT *
FROM image_e
LEFT JOIN image_version_e
    ON image_version_e.imageId = image_e.id

Instead of :

SELECT *
FROM image_version_e
LEFT JOIN image_e
    ON image_e.id = image_version_e.id

Upvotes: 0

Views: 48

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

You are doing left joins so there is a big difference between the two queries.

The first keeps all records in image_e along with matching records in image_version_e.

The second keeps all records in image_version_e along with matching record sin image_e.

Of these two, you should use the one that is semantically correct for your application.

It is quite possible that you really want an inner join:

SELECT *
FROM image_e inner join
     image_version_e
     ON image_version_e.imageId = image_e.id;

With an inner join, the join order shouldn't make a difference (the compiler will choose what it considers to be the best order).

Upvotes: 1

Related Questions