How do i get all the values from 2 tables without doing a Cross Join

user_4778876 0
Viewed 66

I have 2 tables with the following Schema

First ( id, user_id, user_agent, referrer, browser, device_type, IP)

Second ( id, user_id, name, properties)

Table First has a total of 512 entries for user_id 1. Table Second has total of 100 entries for user_id 1. Both tables track different User Activities, so anytime I try to join Table Second on First for user_id 1.

SELECT COUNT(*)
FROM first f
JOIN second AS s ON s.user_id = f.user_id
WHERE f.user_id = 1

I get a total of 51,200 returned rows. Definitely, a Cross Join (first * second) is being done. Is there no way I can get a less enormous returned result? perhaps first + second resul

postgresql sql
2 Answers

I think you can use UNION ALL operator. The SQL UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It does not remove duplicate rows between the various

Each SELECT statement within the UNION ALL must have the same number of fields in the result sets with similar data types. So that you need create same column's with null as ""

Or you can try use UNION

UNION removes duplicate rows. UNION ALL does not remove duplicate rows.

select *
from(

    SELECT id, user_id, user_agent, referrer, browser, device_type, IP, null as "name",
        null as "properties"
        FROM first f
        
        
        UNION ALL
        
        SELECT id, user_id, null as "user_agent", null as "referrer", null as "browser",
        null as "device_type", null as "IP",   name, properties
        FROM second s) x
    Where user_id = 1
user_166238160

use Left join and use the foreign key to query the two tables

user_157083270
Related