title: Postgres returns empty table if lateral join doesnt fetch any rows
body: <p>I run the below query everytime a user logs in to get the relevant data</p>
  
  <pre><code>SELECT ac.username,ac.password,
  (array_agg(json_build_object('message',pm.message,'username',pm.username,'message_id',pm.message_id)))[0:10] AS messages,
  p.project_name,p.project_id
  FROM account ac,
  LATERAL(SELECT * FROM accounts_projects WHERE ac.user_id = accounts_projects.account_id) ap,
  LATERAL(SELECT * FROM project_messages WHERE ap.project_id = project_messages.project_id ORDER BY project_messages.message_id) pm,
  LATERAL(SELECT * FROM project WHERE project.project_id = ap.project_id) p
  WHERE ac.username=$1
  GROUP BY p.project_name,p.project_id,ac.user_id;
  </code></pre>
  
  <p>Lets say i have 2 users - </p>
  
  <p>user 1 has joined projects (accounts_projects table) -> he gets the below output</p>
  
  <pre><code>  username: 'kannaj',
    projects: 
     [ { project_name: 'Machine Learning with Python',
         messages: [Object],
         project_id: 1 },
       { project_name: 'Beethoven with react',
         messages: [Object],
         project_id: 3 },
       { project_name: 'Football with Javascript',
         messages: [Object],
         project_id: 2 } ] }
  </code></pre>
  
  <p>However , if theres a new user who doesnt belong to the accounts_projects. postgres returns an empty row. i.e just fetch ac.user_id &amp; password based on username and null values for other columns?</p>
  
  <p>Is there anyway i can get postgres to <strong>ignore</strong> or return null for the lateral join if there is no data found? It should ideally only fetch ac.username and ac.password.</p>
  
  <p>It does look like a simple LEFT JOIN should do the trick. I tried updating the query like this</p>
  
  <pre><code>LEFT JOIN LATERAL(SELECT * FROM accounts_projects WHERE ac.user_id = accounts_projects.account_id) ap,
  LEFT JOIN LATERAL(SELECT * FROM project_messages WHERE ap.project_id = project_messages.project_id ORDER BY project_messages.message_id) pm,
  LEFT JOIN LATERAL(SELECT * FROM project WHERE project.project_id = ap.project_id) p
  </code></pre>
  
  <p>but i get the the below error</p>
  
  <pre><code>ERROR:  syntax error at or near "JOIN"
  LINE 6: LEFT JOIN LATERAL(SELECT * FROM accounts_projects WHERE ac.u...
               ^
  ********** Error **********
  
  ERROR: syntax error at or near "JOIN"
  SQL state: 42601
  Character: 206
  </code></pre>