I had two tables first one is having all team names data and the other is having only selected teams data. Now I want to get the left-out teams that are there in the first table. How to get the expected output in PostgreSQL?

Table 1

team name
-----------
team1
team2
team3
team4
team5
team6
team7
team9
team10

Table 2

team title
-------------
team1
team2
team3
team4
team7
team8
team9

Required output

team name
-----------
team5
team6
team10

CodePudding user response：

You need a "LEFT JOIN" statement to implement your query. Let's suppose you have 2 tables :

• Table teams with column team_name (and probably many other columns)
• Table teams_data with the foreign key team_title to table teams and many other columns

Now with the LEFT JOIN statement your query is:

SELECT teams.team_name
FROM teams
LEFT JOIN teams_data ON teams_data.team_title = teams.team_name
WHERE teams_data.team_title IS NULL;

The important parts are:

• LEFT JOIN because it retrieves all rows from second tables respecting the ON clause and also not respecting this clause but in this case with all columns set to null
• WHERE ... IS NULL because this statement filters only the rows not in teams_data