CODEWITHSUNDEEP
sql
dorito777
dorito777

Reputation: 23

SQL UNION syntax

I am trying to figure out what the correct syntax for UNION is. My schema looks like is the following:

Players (playerNum, playerName, team, position, birthYear)
Teams = (teamID, teamName, home, leagueName)
Games = (gameID, homeTeamNum, guestTeamNum, date)

I need to print all teamIDs where the team played against the X team but not against the Y team.

So my first idea was to check for the hometeamNum and then do a check for the guesteamNum, but I am not sure home to do the proper syntax.

SELECT DISTINCT hometeamNum 
FROM games 
WHERE 
    guestteamNum IN 
      (SELECT teamid FROM teams WHERE teamname = 'X') AND 
    guestteamNum NOT IN 
      (SELECT teamid FROM teams WHERE teamname = 'Y') 
UNION DISTINCT

Upvotes: 2

Views: 4254

Answers (3)

Paul Maxwell
Paul Maxwell

Reputation: 35563

Using NOT EXISTS allows you to locate rows that don't exist. That is , you want teams that have played against 'X' which are rows that do exist and these can be located by using a simple join and where clause**. Then from those rows you need to find any that do not exist against the team 'Y'.

SELECT DISTINCT
        hometeamnum
FROM games
        INNER JOIN teams AS guests ON games.guestTeamNum = guests.teamID
WHERE guests.teamname = 'X'
AND NOT EXISTS (
        SELECT 1
        FROM games AS games2
                INNER JOIN teams AS guests2 ON games2.guestTeamNum = guests2.teamID
        WHERE games.hometeamnum = games2.hometeamnum
        AND guests2.teamname = 'Y'
        )

Notes.

EXISTS/NOT EXISTS does not actually need to return any data so it is possible to use select 1 or select null or select *. I have used select 1 here simply because it may be easier to understand - however I would personally prefer `select null' which stresses that no data is being returned by the exists subquery.

EXISTS/NOT EXISTS are both reasonably efficient and can perform better than IN (...)

** for performance, and where it does not alter the result, use a join in preference to IN ( subquery )

Upvotes: 0

evsheino
evsheino

Reputation: 2277

If you just need the home teams, this should suffice:

SELECT DISTINCT hometeamnum
FROM games
WHERE guestteamnum NOT IN (SELECT teamid FROM teams WHERE teamname = 'Y')

If you need both home teams and guest teams:

Select all teams that are not 'y' that didn't play agains 'y' as home team and didn't play against 'y' as guest team, and played against 'x' as guest team or played against 'x' as home team.

SELECT DISTINCT teamid
FROM teams
WHERE teamname != 'y' AND teamid NOT IN
 (SELECT hometeamnum
 FROM games INNER JOIN teams ON games.guestteamnum = teams.teamid 
 WHERE teamname = 'y'
 UNION
 SELECT guestteamnum
 FROM games INNER JOIN teams ON games.hometeamnum = teams.teamid 
 WHERE teamname = 'y')
AND teamid IN
 (SELECT guestteamnum
 FROM games INNER JOIN teams on games.hometeamnum = teams.teamid
 WHERE teamname = 'x'
 UNION
 SELECT hometeamnum
 FROM games INNER JOIN teams on games.guestteamnum = teams.teamid
 WHERE teamname = 'x');

Hopefully this is what you were after. There may be a more concise query out there but it's too late in the night for me to think of one :)

Upvotes: 3

Matthew Detweiler
Matthew Detweiler

Reputation: 11

SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;

Upvotes: 0

Related Questions