How to create table with null Join values

Question

Hi all,

I'm only moderately experienced with writing SQL queries (I'm much more of an excel guy) so please bear with. I am trying to create a view based upon joining 5 tables, which is simple enough. But the issue is that a row in this view is only displayed if there is matching content in all 5 tables. If table 4 and 5 doesn't have anything, then this row isn't displayed at all, even though the other 3 tables do match up. It's basically like an all or nothing affair. I want all rows to be displayed even if they don't have the information in the later tables. Table 1, 2, and 3 columns will always be populated, so thats not a concern.

I attached two excel examples so you can see what I'm going for. Source code:

Create or Replace View public.view1 as
Select t1.col1, t1.col2,t2.col1, t2.col2, t3.col1, t3.col2, t4.col1, t4.col2, t5.col1, t5.col2
From Table 2
Join table 3 on table 2
Join table 5 on table 3
Join table 1 on table 5
Join table 4 on table 5

Thanks!

Answer 1

Good day,

It sound in first glance that you are using INNER JOIN while you want to use LEFT/RIGHT/FULL JOIN (depending on your exact requirement)

Please go over this tutorial and learn about the different types of JOIN

https://www.w3schools.com/sql/sql_join.asp

Answer 2

Hi RevNight-3481,

As pituach mentioned, you can use left/right/full join as needed.

The LEFT JOIN clause returns all rows from the left table and the matching rows from the right table. If no matching rows found in the right table, NULL are used.
The RIGHT JOIN clause starts selecting data from the right table and matching with the rows from the left table. If no matching rows found in the left table, NULL are used.
The FULL JOIN caluse returns all rows present in both the Left table and the right table. All the unmatched rows filled with NULL Values.

Please refer to this tutorial which might help.
In addition, if you have any other questions, could you please share us your table structure (CREATE TABLE …) and some sample data (INSERT INTO …) in order to provide further help?

Best Regards,
Amelia

=======================================
Please remember to click " Accept Answer" and upvote the responses that resolved your issue. This can be beneficial to other community members reading this thread.

Answer 3

Hi, if your problem is not resolved, the link below may be useful for you. have a nice day

sql-is-null