JOIN SQL is not working when join table is empty

Question

I am working on a project where multiple table data exists for a user but if empty one table from any of them then SQL return empty data even other tables data exist like below SQL

$id = 40;
SELECT * 
  FROM players a
  JOIN pitcher b 
    ON a.id = b.user_id 
  JOIN outfield_position c 
    ON a.id = c.user_id
  JOIN infield_position d
    ON a.id = d.user_id
  JOIN defensive_statistics e
    ON a.id = e.user_id
  JOIN career f
    ON a.id = f.user_id
 WHERE a.id = $id

like if $outfield_position table is empty then the SQL returns an empty array but sometimes maybe data here, what can I do if data available or not available every way can works?

Thanks

This code looks like it's riddled with [SQL injection holes](http://bobby-tables.com/) so it's worth using placeholder values whenever possible. Why are the table names dynamic? That's usually a sign of an irregular schema that needs to be expressed in [proper relational database terms](https://en.wikipedia.org/wiki/Database_normalization). — tadman, Sep 19 '19 at 18:14

score 0 · Answer 1 · answered Sep 19 '19 at 18:18

The default join used is inner join and as already explained here joins . The output in this case will be intersection of the tables it is joined with,so if any table does not have any value then intersection will give empty result.

You can try with LEFT OUTER JOIN or FULL OUTER JOIN , in Left Outer Join if data is present in $players the value will be displayed.If unsure about the data Full Outer join will give all the results from all the tables.

JOIN SQL is not working when join table is empty

1 Answers1