MySQL – Unable to Run FULL OUTER JOIN

join;MySQLmysql-5.6

Here is my query .

 SELECT user_record.password,user_addresses.street 
 FROM `user_record` 
 FULL OUTER JOIN `user_addresses` 
     ON user_record.id=user_addresses.user_id 
 ORDER by user_record.id

here is the error which is happening when i run my query in Phpmyadmin .

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OUTER JOIN `user_addresses` ON user_record.id=user_addresses.user_id ORDER by us' at line 1

Best Answer

Full outer join is not supported. If you look at:

https://dev.mysql.com/doc/refman/5.7/en/join.html

there is only LEFT and RIGHT outer join. You can however mimic FULL OUTER with a UNION between LEFT and RIGHT:

SELECT user_record.id AS user_id, 
       user_record.password, user_addresses.street 
FROM `user_record` 
LEFT JOIN `user_addresses` 
    ON user_record.id = user_addresses.user_id 

UNION

SELECT user_addresses.user_id, 
       user_record.password, user_addresses.street 
FROM `user_record` 
RIGHT JOIN `user_addresses` 
    ON user_record.id = user_addresses.user_id 

ORDER BY user_id ;

You have to include user_record.id / user_addresses.user_id in your select clause to be able to order by it.

Related Solutions

Mysql – Query WHERE clause and JOIN

Here is your original query

SELECT * FROM `CallDetailRecord`
WHERE `StartTime` >=1357102799000
AND `StartTime` <=1357880399000
JOIN `CallEvent` ON `EventID` = `CallEventID`
LIMIT 1000

In theory, you can do the following

Subquery that has WHERE on the StartTime
Add ORDER BY StartTime
Do the LIMIT inside the subsquery
Specify a LEFT JOIN

Here is such a query

SELECT * FROM
(
    SELECT * FROM `CallDetailRecord`
    WHERE `StartTime` >=1357102799000
    AND `StartTime` <=1357880399000
    ORDER BY StartTime
    LIMIT 0,1000
) A LEFT JOIN `CallEvent` B ON `EventID` = `CallEventID`;

If the query is slow, then add an index on StartTime

ALTER TABLE CallDetailRecord ADD INDEX (StartTime);

Give it a Try !!!

Mysql – Ask about Inner Join & Union

Try This

SELECT A.*,B.mat_nama,C.mats_nama 
FROM pengeluaran AS A 
INNER JOIN material AS B ON A.mat_id = B.mat_id 
INNER JOIN mat_satuan AS C ON B.mat_satuan_id = C.mats_id
WHERE A.mat_id = '7'
   AND A.pengeluaran_tgl BETWEEN '2014-01-01'  AND '2014-02-28'

UNION

SELECT A.*,B.mat_nama,C.mats_nama
FROM penerimaan_sub AS A 
INNER JOIN material AS B ON A.mat_id = B.mat_id
INNER JOIN mat_satuan AS C ON B.mat_satuan_id = C.mats_id
WHERE A.mat_id = '7'
  AND A.penerimaan_tgl BETWEEN '2014-01-01' AND '2014-01-28'

Best Answer

Related Solutions

Mysql – Query WHERE clause and JOIN

Mysql – Ask about Inner Join & Union

Related Question