[SQL] SELECT MINUS SELECT

[akakiwi]

Goede morgen allemaal.

Ik wil het volgende doen

SELECT * FROM TBL_Teams WHERE TeamStrId LIKE 'A%'
MINUS
SELECT * FROM TBL_Teams WHERE TeamStrId LIKE 'BA%'

Maar, nu wil mijn SQL dit niet pikken. In ORACLE SQL kan dit wel. Weet iemand misschien welk statement ik kan gebruiken om de resultaten van 2 statements van elkaar af te trekken?
Ik heb het namelijk niet gevonden in de MSDN library, en ook niet in de "Books Online" die bij je SQL servertje worden meegeleverd.

Alvast bedankt.

[Verwijderd]

Misschien is MINUS inderdaad wel typisch iets voor het Oracle dialect en kent MS SQL server het niet. Dit weet ik echter niet zeker, ik heb hier alleen maar een Oracle SQL boek.

Je kan natuurlijk ook gewoon de query een beetje anders schrijven zodat je iets krijgt als dit:

SELECT * FROM TBL_Teams
WHERE TeamStrId LIKE 'A%'
AND TeamStrId NOT LIKE 'BA%'

Dan ben je van het probleem af.

Je voorbeeld is misschien wat ongelukkig gekozen omdat TeamStrId LIKE 'A%' en TeamStrId LIKE 'BA%' sowieso al mutual exclusive zijn

[Janoz]

Op woensdag 19 december 2001 08:36 schreef akakiwi het volgende:
SELECT * FROM TBL_Teams WHERE TeamStrId LIKE 'A%'
MINUS
SELECT * FROM TBL_Teams WHERE TeamStrId LIKE 'BA%'

Je wilt alles ophalen wat met A begint, en dan alles eraf halen wat met BA begint?

Alles wat met BA begint, begint toch niet met een A, dus wordt toch sowieso niet geselecteerd?

[Verwijderd]

SQLServer kent geen 'MINUS'. Je kunt dat omzeilen door de where clauses te mergen. Maar zoals hierboven al is gezegd sluit de 1e de 2e al uit.

[akakiwi]

Misschien was het voorbeeld wat fout.
Daarom, hier wat ik eigenlijk wil doen.

code:

SELECT C1.Id AS CategoryId, CL1.Name AS CategoryName, 0 
FROM TBL_Teams 
    INNER JOIN TBL_TeamsRights 
        INNER JOIN TBL_Categories C1
            INNER JOIN TBL_CategoriesLanguages CL1
            ON CL1.CategoryId = C1.Id 
        ON C1.Id = TBL_TeamsRights.RightId 
    ON TBL_TeamsRights.TeamStrId = TBL_Teams.StrId 
WHERE TBL_Teams.StrId = @TeamStrId 
AND CL1.LanguageId = @LanguageId 
MINUS
SELECT C1.Id AS CategoryId, CL1.Name AS CategoryName, 1 
FROM TBL_Teams 
    INNER JOIN TBL_TeamsUsers 
        INNER JOIN TBL_Categories C1
            INNER JOIN TBL_CategoriesLanguages CL1
            ON CL1.CategoryId = C1.Id 
        ON C1.Id = TBL_TeamsUsers.RightId 
    ON TBL_TeamsUsers.TeamStrId = TBL_Teams.StrId 
WHERE TBL_Teams.StrId = @TeamStrId 
AND CL1.LanguageId = @LanguageId 
ORDER BY CL1.Name

Iets in deze richting moet het worden.

Uitleg:
Teams hebben rechten op categorieen, maar als gebruikers binnen het team ook eigen rechten hebben, zijn de rechten voor de gebruiker overrulend aan die van het team. Dus, teamrechten - not(rechten - userrechten)
Dan krijg je de effectieve rechten die toegepast moeten worden.
Ik weet het, klinkt een beetje boel kriptisch, maar ik weet even niet hoe ik het anders in weinig woorden moet omschrijven.

Ik ben het nu aan het proberen om binnen de Stored Procedure de NULL (NULL = teamrechten && NOT(NULL) = userrechten) waarden af te vangen, en aan de hand van deze waarde de uitkomst te berekenen.

[Verwijderd]

Je MINUS clause kun je altijd als AND NOT clause aan je 1e where clause toevoegen. Je hoeft dus niet 2 selects te gebruiken, maar slechts 1, waarbij je een where clause hebt gelijkend op:

SELECT * FROM TABLE WHERE bla=foo AND NOT bar=reutel

de bar=foo is zeg maar de clause voor je teams en de AND NOT bar=reutel is de clause voor overrulende rights voor users.

[akakiwi]

Voor de mensen die de oplossing willen weten.
http://www.emeritor.com/dp/werkt.txt

Bedankt voor eventuele hulp en suggesties

[Verwijderd]

Op woensdag 19 december 2001 10:20 schreef Otis het volgende:
Je MINUS clause kun je altijd als AND NOT clause aan je 1e where clause toevoegen. Je hoeft dus niet 2 selects te gebruiken, maar slechts 1, waarbij je een where clause hebt gelijkend op:

SELECT * FROM TABLE WHERE bla=foo AND NOT bar=reutel

Ik denk niet dat dat zo makkelijk is omdat de selects uit verschillende tabellen komt. De eerste select komt uit TBL_Teams, TBL_TeamsRights en TBL_Categories terwijl het 2e deel uit TBL_Teams, TBL_TeamsUsers en TBL_Categories komt...

edit:
Oh, je hebt al iets dat werkt...

[Verwijderd]

Over het algemeen use ik een unique ID voor een bepaald record en kan je dan met NOT IN altijd hetzelfde maken.

Bv:

SELECT [velden]
FROM tabellen
WHERE a LIKE "%B"
AND uID NOT IN
(
SELECT uID
FROM tabellen
WHERE a LIKE "%A"
)

Netjes ANSI SQL
Voor performance zijn stored procs toch niet echt het einde hoor, SQL kan SQL Server veel makkelijker optimizen, vooral bij een dergelijk simpele query..

[dusty]

iets wat met A begint begint nooit met BA.. dus onzinnige query.. dus is hetzelfde als de volgende select:

code:

SELECT * FROM TBL_Teams WHERE TeamStrId LIKE 'A%'

[Goodielover]

Volgens mij is dit 'm.
De tabel Team is trouwens helemaal niet nodig. Ik heb 'm dus ook weggelaten.

code:

SELECT C1.Id AS CategoryId
    ,CL1.Name AS CategoryName
FROM   TBL_Categories C1
     INNER JOIN TBL_CategoriesLanguages CL1 ON CL1.CategoryId = C1.Id
WHERE  CL1.LanguageId = @LanguageId
AND    T.StrId = @TeamStrId
AND    C1.ID IN
    (SELECT T1.RightId
     FROM   TBL_TeamsRights T1
     WHERE  T1.TeamStrId=@TeamStrId
    )
AND    C1.ID NOT IN
    (SELECT T2.RightId
     FROM   TBL_TeamsUsers T2
     WHERE  T2.TeamStrId=@TeamStrId
    )
ORDER BY CL1.Name

Jouw statement zou trouwens nooit de minus uitvoeren, omdat je aan de records uit de eerste select een '0' toevoegt en aan de tweede een '1'.

Doet deze wat je wilt?

[whoami]

Op woensdag 19 december 2001 09:40 schreef rmk het volgende:
Misschien is MINUS inderdaad wel typisch iets voor het Oracle dialect en kent MS SQL server het niet. Dit weet ik echter niet zeker, ik heb hier alleen maar een Oracle SQL boek.

Je kan natuurlijk ook gewoon de query een beetje anders schrijven zodat je iets krijgt als dit:

SELECT * FROM TBL_Teams
WHERE TeamStrId LIKE 'A%'
AND TeamStrId NOT LIKE 'BA%'

Dan ben je van het probleem af.

Je voorbeeld is misschien wat ongelukkig gekozen omdat TeamStrId LIKE 'A%' en TeamStrId LIKE 'BA%' sowieso al mutual exclusive zijn

Even off-topic; Een not like gebruik ik niet graag, want dat vertraagt uw query redelijk. Het DBMS kan dan nl. geen gebruik maken van de indexen die je gedefinieerd hebt, met als gevolg dat er een sequential scan moet gebeuren op de table. Lekker traag, kan je ondertussen

[dusty]

Op woensdag 19 december 2001 10:20 schreef Otis het volgende:
Je MINUS clause kun je altijd als AND NOT clause aan je 1e where clause toevoegen. [...]

Nope, niet altijd.

[dusty]

Op woensdag 19 december 2001 18:39 schreef whoami het volgende:
[..]
Even off-topic; Een not like gebruik ik niet graag, want dat vertraagt uw query redelijk. Het DBMS kan dan nl. geen gebruik maken van de indexen die je gedefinieerd hebt, met als gevolg dat er een sequential scan moet gebeuren op de table. Lekker traag, kan je ondertussen

Nope,
Dan zou je ook geen where X=5 moeten kunnen gebruiken want dan zou jouw DBMS ook geen index ervoor kunnen gebruiken. Of het wordt tijd dat je een betere DBMS gaat gebruiken.

[Goodielover]

Op donderdag 20 december 2001 08:50 schreef dusty het volgende:

[..]

Nope, niet altijd.

Heb je een tegenvoorbeeld?

Ik zou zeggen dat ...

code:

select veld1, veld2, veld3,
from   tabel1, ....
where  veld5 like '%bla%'
and   (veld1, veld2, veld3) NOT IN
     select veld1, veld2, veld3,
     from   tabel2, ....
     where  ......
    )

... behoorlijk een MINUS na doet

[whoami]

Op donderdag 20 december 2001 08:53 schreef dusty het volgende:

[..]

Nope,
Dan zou je ook geen where X=5 moeten kunnen gebruiken want dan zou jouw DBMS ook geen index ervoor kunnen gebruiken. Of het wordt tijd dat je een betere DBMS gaat gebruiken.

OK, ik was misschien niet volledig.
Een NOT LIKE (evenals een IS NULL, <>, NOT EXISTS en een LIKE '%abc') kunnen ervoor zorgen dat er een table scan uitgevoerd wordt ipv een index te gebruiken.

http://www.sql-server-performance.com/transact_sql.asp

[Verwijderd]

Op donderdag 20 december 2001 08:50 schreef dusty het volgende:

[..]

Nope, niet altijd.

Jazeker wel, want de MINUS filtert results uit de al bestaande tijdelijke resultset op basis van de clause die je aan de select NA de minus toevoegt. maw: je kunt die clause van die select ook middels een AND NOT ( je clause ) toevoegen aan de query die de tijdelijke resultset oplevert VOOR je minus.

[Goodielover]

Op donderdag 20 december 2001 20:25 schreef Otis het volgende:

[..]

Jazeker wel, want de MINUS filtert results uit de al bestaande tijdelijke resultset op basis van de clause die je aan de select NA de minus toevoegt. maw: je kunt die clause van die select ook middels een AND NOT ( je clause ) toevoegen aan de query die de tijdelijke resultset oplevert VOOR je minus.

Jouw oplossing werkt alleen als de eerste select en tweede (na de minus) dezelfde tabellen hebben en de conditie ook nog hetzelfde record betreft.

code:

select achternaam
from   persoon
minus
select naam
from   woonplaatsen

Dit gaat je niet lukken met een AND NOT constructie.

Mijn constructie met

code:

AND naam NOT IN
    (select...
    )

zal wel werken (zie boven voor uitgebreidere uitwerking ihkv dit topic).

[Verwijderd]

een MINUS vereist 2 selects die exact dezelfde recordset structuur opleveren, net als UNION.

[JaQ]

Op donderdag 20 december 2001 18:50 schreef whoami het volgende:

[..]

OK, ik was misschien niet volledig.
Een NOT LIKE (evenals een IS NULL, <>, NOT EXISTS en een LIKE '%abc') kunnen ervoor zorgen dat er een table scan uitgevoerd wordt ipv een index te gebruiken.

http://www.sql-server-performance.com/transact_sql.asp

Helaas geld dat ook voor een Oracle dbms. Tevens trunc (datumveld) zorgt ervoor dat je indexes niet gebruikt worden. (basis performance en tuning van sql scripten).

Uiteraard heb je het dan nog niet of optimize routes (rule, danwel cost based), tablevolgordes etc.

[justmental]

Op donderdag 20 december 2001 20:25 schreef Otis het volgende:

[..]

Jazeker wel, want de MINUS filtert results uit de al bestaande tijdelijke resultset op basis van de clause die je aan de select NA de minus toevoegt. maw: je kunt die clause van die select ook middels een AND NOT ( je clause ) toevoegen aan de query die de tijdelijke resultset oplevert VOOR je minus.

Toch niet helemaal.
Minus doet automatisch een distinct, and not niet.

[justmental]

Op vrijdag 21 december 2001 14:54 schreef DrFrankenstoner het volgende:

[..]

Helaas geld dat ook voor een Oracle dbms. Tevens trunc (datumveld) zorgt ervoor dat je indexes niet gebruikt worden. (basis performance en tuning van sql scripten).

Uiteraard heb je het dan nog niet of optimize routes (rule, danwel cost based), tablevolgordes etc.

Trunc (datumveld) kan wel een function based index gebruiken.

[dusty]

Op vrijdag 21 december 2001 15:04 schreef justmental het volgende:
[..]
Toch niet helemaal.
[..]

[Verwijderd]

dat boeit toch niet of er wel of niet een distinct gedaan wordt? Of je nu 3 keer filtert op A=1 of 1 keer, allebei de keren komen records met A=1 er niet door.

[justmental]

Op vrijdag 21 december 2001 15:46 schreef Otis het volgende:
dat boeit toch niet of er wel of niet een distinct gedaan wordt? Of je nu 3 keer filtert op A=1 of 1 keer, allebei de keren komen records met A=1 er niet door.

Je krijgt een ander resultaat.
Niet door de records die eruit gefilterd worden, maar door de distinct op de rest.
als je bijvoorbeeld:

code:

select kolom
from tabel
where kolom not in (1)

hebt en je hebt 2 records met kolom=1 en 3 records met kolom=2 dan krijg je met de minus variant 1 record terug en met deze 3.

[Verwijderd]

Op vrijdag 21 december 2001 15:53 schreef justmental het volgende:
Je krijgt een ander resultaat.
Niet door de records die eruit gefilterd worden, maar door de distinct op de rest.
als je bijvoorbeeld:

code:

select kolom
from tabel
where kolom not in (1)

hebt en je hebt 2 records met kolom=1 en 3 records met kolom=2 dan krijg je met de minus variant 1 record terug en met deze 3.

Nee dit klopt niet.

tabel:
[id*][kolom]

records:

code:

id | kolom
-----------
 1 |  1
 2 |  1
 3 |  2
 4 |  2
 5 |  2

dan krijg je bij
SELECT * FROM table
MINUS
SELECT * FROM table where kolom in (1)

3 records, want ze zijn niet gelijk, distinct werkt dus niet.

Idem bij de query
SELECT * FROM table where kolom not in (1)

[justmental]

code:

SQL> create table temp (kolom number);

Tabel is gecreëerd.

SQL> insert into temp values (1);

1 rij is gecreëerd.

SQL> /

1 rij is gecreëerd.

SQL> /

1 rij is gecreëerd.

SQL> insert into temp values (2);

1 rij is gecreëerd.

SQL> insert into temp values (2);

1 rij is gecreëerd.

SQL> select * from temp where kolom not in (1);

     KOLOM
----------
       2
       2

SQL> select * from temp
  2  minus
  3  select * from temp where kolom=1;

     KOLOM
----------
       2

niet?
De combinatie van kolommen was gewoon uniek in jouw geval.

[Verwijderd]

ja DUUUUUH! die table heeft 1 column, en JA dan zijn ze gelijk, maar bij records waar meerdere columns in zitten heb je niets aan die distinct en levert hij meerdere records op.

Op zich is die distinct wel te verklaren uit optimalizatie oogpunt maar logischerwijs absoluut niet. Als je een distinct wilt, dan plaats je dat er toch bij in de select clause! raar dat dat een feature is van die minus clause.

btw: We praten hier over MySQL of oracle?

[justmental]

Op vrijdag 21 december 2001 16:29 schreef Otis het volgende:
btw: We praten hier over MySQL of oracle?

Speelgoed-databases hebben deze functionaliteit niet.

[justmental]

Op vrijdag 21 december 2001 16:29 schreef Otis het volgende:
Op zich is die distinct wel te verklaren uit optimalizatie oogpunt maar logischerwijs absoluut niet.

Ik heb het ook niet bedacht, maar het is gewoon zo.
Dus is het iets om rekening mee te houden.

[Verwijderd]

ok.

Mja, SQLserver heeft het niet, maar met DISTINCT ROW en een AND NOT lukt het dus wel. ach.