If there are any disadvantage by not to use primary table / key in join statement

Question

I have following tables;

users 
    id
    name

departments 
    id
    name

user_departments
    id
    user_id
    name

user_views
    id
    user_id
    views

Above id is primary key in all tables, while user_id and department_id are foreign keys.

I need to make a query for getting users view in department. So I made query like that

SELECT user_views.view FROM user_views JOIN user_departments ON user_views.user_id = user_departments.user_id WHERE user_departments.department_id = 1234;

Above query not using primary tables users and departments at all, So join only on foreign key columns in two relation tables.

Is there performance or other disadvantage for not using primary tables and respective primary key in join ? I am using postgres sql, However I have same question about MySQL too.

UPDATE: Alternative versions I am talking about

SELECT user_views.view FROM users JOIN user_views ON users.id = user_views.user_id JOIN user_departments ON users.id = user_departments.user_id WHERE user_departments.department_id = 1234;

SELECT user_views.view FROM users JOIN user_views ON users.id = user_views.user_id JOIN user_departments ON users.id = user_departments.user_id JOIN departments ON departments.id = user_departments.department_id  WHERE departments.id = 1234;

score 2 · Answer 1 · answered Sep 12 '18 at 01:59

PKs (primary keys), FKs (foreign keys) & other constraints are not needed to query. Any 2 tables can be meaningfully joined on any condition. The meanings of the base tables & query results (the condition for a row being in the table or not in it) are necessary & sufficient. Constraints allow the DBMS to disallow associated invalid database states & to optimize. Write a query that returns the rows you are interested in.

Required to join 2 tables with their FKs in a 3rd table
Why aren't primary key / foreign key matches used for joins?
Is there any rule of thumb to construct SQL query from a human-readable description?

When constraints hold, certain queries that would otherwise sometimes return different results always return the same result. Then if you have a choice, using column sets that are indexed, which PKs & FKs typically are, rather than those that aren't, can improve performance. But that isn't particular to PKs & FKs. In fact we add indexes for column sets when we expect that the index maintenance is worth the query performance improvement. PKs & FKs are just a special case of that.

ScaisEdge · Accepted Answer · 2018-09-17T15:45:25.507

if you don't use primary key because your query need the join based on others columns

be sure you have proper index in ON and WHERE involved columns

    SELECT user_views.view 
    FROM user_views 
    JOIN user_departments ON user_views.user_id = user_departments.user_id 
    WHERE user_departments.department_id = 123

in your case

on  table  user_views composite  index on (user_id, view)

the view column is added for obatain the value directly form the index and avoid access to data table too

on table user_departments index on (department_id, user_id)

in this way the performance are properly managed ..

Updated for OP updated version

do the fact the tables are all completed by primary and foreign key the difference between the two queries should be very small .. (if appreciable) you can take a look an the related explain plan for see the column and the index involved and the strategies adopted by sql engine

SELECT user_views.view 
FROM users JOIN user_views ON users.id = user_views.user_id 
JOIN user_departments ON users.id = user_departments.user_id 
WHERE user_departments.department_id = 1234;


SELECT user_views.view 
FROM users 
JOIN user_views ON users.id = user_views.user_id 
JOIN user_departments ON users.id = user_departments.user_id 
JOIN departments ON departments.id = user_departments.department_id  
WHERE departments.id = 1234;

best performance could be obtained usimg composite index gaining all the info for filtering and select directly in index table and avoiding access (and waste of time) to the table data ..

user_id and department_id is foreign key in both tables, So single column index is already added but not composite index If still their would be any difference ? — kuldeep.kamboj, Sep 11 '18 at 12:29
yes .. a composite index get all the information neede by the join and don't require an access to data table for get others data .. and this for performance is really useful — ScaisEdge, Sep 11 '18 at 12:39
Ok I get it, But as per my original question I want to know difference between to use masters + child tables with primary key + foreign keys and use only child tables with foreign keys ( I updated question to add alternative versions ) — kuldeep.kamboj, Sep 11 '18 at 12:55
@kuldeep.kamboj answer updated .. between the two queries should be very small difference (if appreciabile) .. — ScaisEdge, Sep 11 '18 at 13:05

If there are any disadvantage by not to use primary table / key in join statement

2 Answers2