SQL Join Types: Anti-Semi Join

Tables

Table 1: Suppliers

SupplierID

SupplierName

ContactName

Address

City

PostalCode

Country

Phone

10

Refrescos Americanas LTDA

Carlos Diaz

Av. das Americanas 12.890

São Paulo

5442

Brazil

(11) 555 4640

21

Lyngbysild

Niels Petersen

Lyngbysild Fiskebakken 10

Lyngby

2800

Denmark

43844108

22

Zaanse Snoepfabriek

Dirk Luchte

Verkoop Rijnweg 22

Zaandam

9999 ZZ

Netherlands

(12345) 1212

Table 2: Products

ProductID

ProductName

SupplierID

CategoryID

Unit

Price

24

Guaraná Fantástica

10

1

12 – 355 ml cans

4.5

45

Røgede sild

21

8

1k pkg.

9.5

46

Spegesild

21

8

4 – 450 g glasses

12

47

Zaanse koeken

22

3

10 – 4 oz boxes

9.5

48

Chocolade

22

3

10 pkgs.

12.75

Query

If you need a place to try this query, try here: https://www.w3schools.com/sql/trysql.asp?filename=trysql_op_in

SELECT SupplierName
FROM Suppliers
WHERE NOT EXISTS (
  SELECT ProductName 
  FROM Products 
  WHERE SupplierId = Suppliers.supplierId 
    AND Price > 13
);

Result

SupplierName

Refrescos Americanas LTDA

Lyngbysild

Zaanse Snoepfabriek

© 2019, Alejandro G. Carlstein Ramos Mejia. All rights reserved.

Share

Leave a Reply

Your email address will not be published. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

*

Click to Insert Smiley

SmileBig SmileGrinLaughFrownBig FrownCryNeutralWinkKissRazzChicCoolAngryReally AngryConfusedQuestionThinkingPainShockYesNoLOLSillyBeautyLashesCuteShyBlushKissedIn LoveDroolGiggleSnickerHeh!SmirkWiltWeepIDKStruggleSide FrownDazedHypnotizedSweatEek!Roll EyesSarcasmDisdainSmugMoney MouthFoot in MouthShut MouthQuietShameBeat UpMeanEvil GrinGrit TeethShoutPissed OffReally PissedMad RazzDrunken RazzSickYawnSleepyDanceClapJumpHandshakeHigh FiveHug LeftHug RightKiss BlowKissingByeGo AwayCall MeOn the PhoneSecretMeetingWavingStopTime OutTalk to the HandLoserLyingDOH!Fingers CrossedWaitingSuspenseTremblePrayWorshipStarvingEatVictoryCurseAlienAngelClownCowboyCyclopsDevilDoctorFemale FighterMale FighterMohawkMusicNerdPartyPirateSkywalkerSnowmanSoldierVampireZombie KillerGhostSkeletonBunnyCatCat 2ChickChickenChicken 2CowCow 2DogDog 2DuckGoatHippoKoalaLionMonkeyMonkey 2MousePandaPigPig 2SheepSheep 2ReindeerSnailTigerTurtleBeerDrinkLiquorCoffeeCakePizzaWatermelonBowlPlateCanFemaleMaleHeartBroken HeartRoseDead RosePeaceYin YangUS FlagMoonStarSunCloudyRainThunderUmbrellaRainbowMusic NoteAirplaneCarIslandAnnouncebrbMailCellPhoneCameraFilmTVClockLampSearchCoinsComputerConsolePresentSoccerCloverPumpkinBombHammerKnifeHandcuffsPillPoopCigarette