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

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

CodePudding user response：

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

CodePudding user response：

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

CodePudding user response：

With current join you will get this much results, even left join and right join won't help, If you want to concatenate results you may try UNION Clause or please tell what exactly you are looking for ?

CodePudding user response：

I'm mot shure, but you can try do this one if you want to get data by user:

SELECT *
FROM first as f, second as s
WHERE s.user_id = f.user_id
ORDER BY s.user_id;