PostgreSQL doesn't recognize STRAIGHT_JOIN

Question

SELECT  STRAIGHT_JOIN s.*, date_part('epoch', s.date) AS unixdate, 
  date_part('epoch', s.expire) as expireunix, u.username, u.fullname, 
  u.photo, u.email, t.topic, t.imageurl  
FROM stories s, users u, topics t WHERE (s.uid = u.uid) AND (s.tid = t.tid)

It says:

ERROR: syntax error at or near "s"
SQL state: 42601
Character: 23

I'm doing a join but its giving me that error. If I remove the STRAIGHT_JOIN keyword it executes without any problems. But are there any negatives to remove it?

Answer 1

STRAIGHT_JOIN is a mysql enhancement, by pgsql, are you referring to a postgres database? If so, you should look at SQL standard operations. The postgres manual calls out the standards compliance level of each of their operations. Good to know if you are going to be moving between dbmses.

Look at www.postgresql.org/docs/8.3/static/tutorial-join.html

Answer 2

Just leave it out. STRAIGHT_JOIN is a MySQL enhancement that works like INNER JOIN but STRAIGHT_JOIN forces the left table to be read first. It's a way of overriding the MySQL optimizer, when the optimizer makes a poor decision reordering tables.

All this is irrelevant to PostgreSQL. Not only is the STRAIGHT_JOIN syntax keyword specific to MySQL, but its function of influencing the internals of the optimizer is too. Each brand of database has its own optimizer implementation, and each has its own idiosyncrasies.

Also you can use standard SQL-92 JOIN syntax instead of the obsolete comma-style joins. Here's how I would write this query:

SELECT s.*, DATE_PART('epoch', s.date) AS unixdate, 
  DATE_PART('epoch', s.expire) AS expireunix, u.username, 
  u.fullname, u.photo, u.email, t.topic, t.imageurl 
FROM stories s 
  INNER JOIN users u ON (s.uid = u.uid)
  INNER JOIN topics t ON (s.tid = t.tid);

Answer 3

STRAIGHT_JOIN is a hint that enforces JOIN order in MySQL.

PostgreSQL developers dislike hints, that's why PostgreSQL lacks this feature.

If you don't know / care what the JOIN order is, just omit this keyword:

SELECT  s.*, date_part('epoch', s.date) AS unixdate, 
        date_part('epoch', s.expire) as expireunix, u.username, u.fullname, 
        u.photo, u.email, t.topic, t.imageurl  
FROM    stories s, users u, topics t
WHERE   s.uid = u.uid
        AND s.tid = t.tid

MySQL doesn't implement any JOIN methods except for NESTED LOOPS, but PostgreSQL does.

JOIN order makes no sense for MERGE JOIN and cannot be forced in PostgreSQL for HASH JOIN.

You can make some of the columns non-sargable and this will force PostgreSQL to use intended JOIN order in case the optimizer chooses NESTED LOOPS:

SELECT  s.*, date_part('epoch', s.date) AS unixdate, 
        date_part('epoch', s.expire) as expireunix, u.username, u.fullname, 
        u.photo, u.email, t.topic, t.imageurl  
FROM    stories s, users u, topics t
WHERE   s.uid = (u.uid + 1) - 1
        AND t.tid = (s.tid + 1) - 1

This will force JOIN order in case NESTED LOOPS will be chosen by optimizer.

To force NESTED LOOPS, get rid or the equijoin in the query:

SELECT  s.*, date_part('epoch', s.date) AS unixdate, 
        date_part('epoch', s.expire) as expireunix, u.username, u.fullname, 
        u.photo, u.email, t.topic, t.imageurl  
FROM    stories s, users u, topics t
WHERE   s.uid > u.uid - 1
        AND s.uid < u.uid + 1
        AND t.tid > s.tid - 1
        AND t.tid < s.tid + 1