Sql – PostgreSQL doesn’t recognize STRAIGHT_JOIN

postgresql

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?

Best Solution

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);
Related Question