How to show data that's not in a table. SQL ORACLE-CodePudding

I've a data base with two tables.

Table Players               Table Wins
ID   Name                     ID    Player_won
1    Mick                     1        2
2    Frank                    2        1
3    Sarah                    3        4
4    Eva                      4        5
5    Joe                      5        1

I need a SQL query which show "The players who have not won any game". I tried but I don't know even how to begin. Thank you

CodePudding user response：

You need all the rows from players that don't have corresponding rows in wins. For this you need a left join, filtering for rows that don't join:

select
  p.id,
  p.name
from Players p
left join Wins w on w.Player_won = p.id
where w.Player_won is null

You can also use not in:

select
  id,
  name
from Players
where id not in (select Player_won from Wins)

CodePudding user response：

I think you should provide your attempts next time, but here you go:

select p.name
  from players p
 where not exists (select * from wins w where p.id = w.player_won);

MINUS is not the best option here because of not using indexes and instead performing a full-scan of both tables.

CodePudding user response：

I've a data base with two tables.

You don't show the names or any definition of the tables, leaving me to make an educated guess about their structure.

I tried but I don't know even how to begin.

What exactly did you try? Possibly what you are missing here is the concept of a LEFT OUTER JOIN.

Assuming the tables are named player_table and wins_table, and have column names exactly as you showed, and without knowing whether or not wins_table has rows for players with zero wins… this will do it:

select Name
from players_table pt
left join wins_table wt on (pt.ID = wt.ID)
where nvl(Player_won, 0) = 0;

CodePudding user response：

How about the MINUS set operator?

Sample data:

SQL> with players (id, name) as
  2    (select 1, 'Mick'   from dual union all
  3     select 2, 'Ffrank' from dual union all
  4     select 3, 'Sarah' from dual union all
  5     select 4, 'Eva' from dual union all
  6     select 5, 'Joe' from dual
  7    ),
  8  wins (id, player_won) as
  9    (select 1, 2 from dual union all
 10     select 2, 1 from dual union all
 11     select 3, 4 from dual union all
 12     select 4, 5 from dual union all
 13     select 5, 1 from dual
 14    )

Query begins here:

 15  select id from players
 16  minus
 17  select player_won from wins;

        ID
----------
         3

SQL>

So, yes ... player 3 didn't win any game so far.

CodePudding user response：

SELECT * 
FROM Players p 
INNER JOIN Wins w
ON p.ID = w.ID
WHERE w.players_won = 0

I have not done SQL in awhile but I think this might be right if you are looking for players with 0 wins