CODEWITHSUNDEEP
sqlpostgresql
Lee Eather
Lee Eather

Reputation: 355

Join two tables with no relation postgres?

I have this statement which you can see

SELECT t1.*, t2.* FROM
(SELECT m.* FROM microposts AS m) AS t1
  FULL JOIN
(SELECT r.* FROM ratings AS r) AS t2
ON true

I am using Rails and connecting to the database raw, but the output removes duplicate named columns eg user_id etc from the second table and is still giving results in the second table in regards to the first even though there is no relation. Eg

+------+-----------+-------+--------+
| m.id | m.content | r.id  | rating |  
+------+-----------+-------+--------+
|  1   |  "hello"  |  10   |   5    |
+------+-----------+-------+--------+

There is no relation between table m and r

I would like A output of something like this

+------+-----------+------+---------+
| m.id | m.content | r.id | rating  |  
+------+-----------+------+---------+
|  1   |  "hello"  | null |   null  |
| null |   null    |  5   |    4    |
|  2   |   "gday"  | null |   null  |
+------+-----------+------+---------+
....................... etc

Upvotes: 4

Views: 3165

Answers (1)

Serg
Serg

Reputation: 22811

This is rather exotic way to say UNION ALL

SELECT t1.*, t2.* 
FROM
   (SELECT m.* FROM microposts AS m) AS t1
FULL JOIN
   (SELECT r.* FROM ratings AS r) AS t2
ON false

Contrary, ON true will create a cartesian product.

Upvotes: 4

Related Questions