U ovom SQL članku naučit ćete kako koristiti IN ključnu riječ u upitima SQL baze podataka.

Što je ključna riječ IN u SQL-u?

The U je logički operator u jeziku strukturiranih upita koji korisnicima baze podataka omogućuje definiranje više od jedne vrijednosti u klauzuli WHERE.

WHERE klauzula s IN operatorom prikazuje one zapise u rezultatu koji se podudaraju s danim skupom vrijednosti. Također možemo specificirati podupit u zagradama IN operatora.

znak u niz java

Operator IN možemo koristiti s upitima INSERT, SELECT, UPDATE i DELETE u SQL bazi podataka.

Operator IN u SQL-u zamjenjuje proces višestrukih uvjeta ILI u upitima.

Sintaksa operatora IN:

 SELECT Column_Name_1, Column_Name_2, Column_Name_3, ......, Column_Name_N FROM Table_Name WHERE Column_Name IN (Value_1, Value_2, Value_3, ......., Value_N); 

Ako želite koristiti IN operator u SQL izjavama, morate slijediti dolje navedene korake istim redoslijedom:

1. Napravite bazu podataka u SQL-u.
2. Napravite novu SQL tablicu.
3. Unesite podatke u tablicu.
4. Pregledajte umetnute podatke.
5. Koristite SQL IN operator za prikaz podataka tablice.

Sada ćemo ukratko objasniti svaki korak jedan po jedan s najboljim SQL primjerom:

Korak 1: Stvorite jednostavnu novu bazu podataka

Prvi korak je stvaranje nove baze podataka u Structured Query Language.

Sljedeća naredba CREATE stvara new Strojarski_koledž Baza podataka u SQL poslužitelju:

 CREATE Database Mechanical_College; 

Korak 2: Napravite novu tablicu

Sada upotrijebite sljedeću SQL sintaksu koja pomaže u stvaranju nove tablice u bazi podataka:

 CREATE TABLE table_name ( 1st_Column data type (character_size of 1st Column), 2nd_Column data type (character_size of the 2nd column ), 3rd_Column data type (character_size of the 3rd column), ... 

 Nth_Column data type (character_size of the Nth column) ); 

Sljedeća naredba CREATE stvara Fakultet_Info stol u Strojarski_koledž Baza podataka:

 CREATE TABLE Faculty_Info ( Faculty_ID INT NOT NULL PRIMARY KEY, Faculty_First_Name VARCHAR (100), Faculty_Last_Name VARCHAR (100), Faculty_Dept_Id INT NOT NULL, Faculty_Joining_DateDATE, Faculty_City Varchar (80), Faculty_Salary INT ); 

Korak 3: Umetnite podatke u tablicu

Sljedeći INSERT upiti umeću zapise fakulteta u tablicu Faculty_Info:

 INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1001, Arush, Sharma, 4001, 2020-01-02, Delhi, 20000); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1002, Bulbul, Roy, 4002, 2019-12-31, Delhi, 38000 ); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1004, Saurabh, Sharma, 4001, 2020-10-10, Mumbai, 45000); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1005, Shivani, Singhania, 4001, 2019-07-15, Kolkata, 42000); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1006, Avinash, Sharma, 4002, 2019-11-11, Delhi, 28000); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary)VALUES (1007, Shyam, Besas, 4003, 2021-06-21, Lucknow, 35000); 

Korak 4: Pregledajte umetnute podatke

Sljedeća izjava SELECT prikazuje podatke tablice Faculty_Info:

 SELECT * FROM Faculty_Info; 

Korak 5: Upotrijebite IN operator za pregled podataka tablice Faculty_Info na različite načine

Sljedeći upit koristi numeričke vrijednosti s IN operatorom:

 SELECT Faculty_Id, Faculty_First_Name, Faculty_Dept_Id, Faculty_Joining_Date, Faculty_Salary FROM Faculty_Info WHERE Faculty_Salary IN ( 38000, 42000, 45000, 35000); 

Ovaj upit prikazuje samo zapis onih fakulteta čija je plaća proslijeđena u IN operatoru WHERE klauzule.

Izlaz:

Sljedeći upit koristi tekstualne ili znakovne vrijednosti s IN logičkim operatorom:

 SELECT Faculty_Id, Faculty_First_Name, Faculty_Joining_Date, Faculty_City FROM Faculty_Info WHERE Faculty_City IN ( Mumbai, Kolkata, Lucknow); 

Ovaj upit prikazuje samo zapis onih fakulteta čiji je grad uključen u zagrade IN operatora u WHERE klauzuli.

Izlaz:

Sljedeći upit koristi format DATE s logičkim operatorom IN:

 SELECT Faculty_Id, Faculty_First_Name, Faculty_Dept_ID Faculty_Joining_Date, Faculty_Salary FROM Faculty_Info WHERE Faculty_Joining_Date IN (2020-01-02, 2021-06-21, 2020-10-10, 2019-07-15); 

Ovaj upit prikazuje samo one zapise fakulteta čiji je datum pridruživanja proslijeđen u IN operatoru WHERE klauzule.

Izlaz:

Sljedeći upit koristi SQL naredbu UPDATE s logičkim operatorom IN:

 UPDATE Faculty_Info SET Faculty_Salary = 50000 WHERE Faculty_Dept_ID IN (4002, 4003); 

Ovaj upit ažurira plaću onih fakulteta čiji je Dept_Id proslijeđen u IN operatoru WHERE klauzule.

Da provjerite rezultat gornjeg upita, upišite sljedeći SELECT upit u SQL:

 SELECT * FROM Faculty_Info; 

SQL IN operator s podupitom

U jeziku strukturiranih upita također možemo koristiti podupit s logičkim operatorom IN.

Sintaksa IN operatora s podupitom je dana u nastavku:

ti si spoj

 SELECT Column_Name_1, Column_Name_2, Column_Name_3, ......, Column_Name_N FROM Table_Name WHERE Column_Name IN (Subquery); 

Ako želite razumjeti IN operator s podupitom, morate stvoriti dvije različite tablice u Structured Query Language koristeći naredbu CREATE.

Sljedeći upit stvara tablicu Faculty_Info u bazi podataka:

 CREATE TABLE Faculty_Info ( Faculty_ID INT NOT NULL PRIMARY KEY, Faculty_First_Name VARCHAR (100), Faculty_Last_Name VARCHAR (100), Faculty_Dept_Id INT NOT NULL, Faculty_Address Varchar (80), Faculty_City Varchar (80), Faculty_Salary INT ); 

Sljedeći upit stvara Odjel_Informacije tablica u bazi podataka:

 CREATE TABLE Department_Info ( Dept_Id INT NOT NULL, Dept_Name Varchar(100), Head_Id INT ); 

Sljedeći INSERT upiti umeću zapise fakulteta u tablicu Faculty_Info:

 INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1001, Arush, Sharma, 4001, 22 street, New Delhi, 20000); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1002, Bulbul, Roy, 4002, 120 street, New Delhi, 38000 ); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1004, Saurabh, Sharma, 4001, 221 street, Mumbai, 45000); INSERT INTO Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Address, Faculty_City, Faculty_Salary) VALUES (1005, Shivani, Singhania, 4001, 501 street, Kolkata, 42000); 

Sljedeći upiti INSERT umeću zapise odjela u tablicu Department_Info:

što je ymail

 INSERT INTO Department_Info (Dept_ID, Dept_Name, Head_Id) VALUES ( 4001, Arun, 1005); INSERT INTO Department_Info (Dept_ID, Dept_Name, Head_Id) VALUES ( 4002, Zayant, 1009); INSERT INTO Department_Info (Dept_ID, Dept_Name, Head_Id) VALUES ( 4003, Manish, 1007); 

Sljedeća izjava SELECT prikazuje podatke tablice Faculty_Info:

 SELECT * FROM Faculty_Info; 

Sljedeći upit prikazuje zapise odjela iz tablice Department_Info:

 SELECT * FROM Department_Info; 

Sljedeći upit koristi IN operator s podupitom:

 SELECT * FROM Faculty_Info WHERE Faculty_Dept_Id IN ( Select Dept_Id FROM Department_Info WHERE Head_Id >= 1007); 

Ovaj upit prikazuje zapis onih fakulteta čiji se Dept_ID tablice Faculty_Info podudara s Dept_Id tablice Department_Info.

Izlaz:

Što NIJE IN u SQL-u?

NOT IN je još jedan operator u Structured Query Language, koji je upravo suprotan SQL IN operatoru. Omogućuje vam pristup tim vrijednostima iz tablice, koje ne prolaze u zagradi IN operatora.

Operator NOT IN može se koristiti u SQL upitima INSERT, UPDATE, SELECT i DELETE.

Sintaksa operatora NOT IN:

 SELECT Column_Name_1, Column_Name_2, Column_Name_3, ......, Column_Name_N FROM Table_Name WHERE Column_Name NOT IN (Value_1, Value_2, Value_3, ......., Value_N); 

Ako želite koristiti operator NOT IN u izjavama SQL-a, morate slijediti navedene korake istim redoslijedom:

1. Kreirajte bazu podataka u SQL sustavu.
2. Napravite novu tablicu u bazi podataka.
3. Unesite podatke u tablicu
4. Pregledajte umetnute podatke
5. Koristite NOT IN operator za pregled podataka.

Sada ćemo ukratko objasniti svaki korak jedan po jedan s najboljim SQL primjerom:

Korak 1: Stvorite jednostavnu novu bazu podataka

alisa manyonok

Sljedeći upit stvara novi Civilna_industrija Baza podataka u SQL poslužitelju:

 CREATE Database Industry; 

Korak 2: Napravite novu tablicu

Sljedeći upit stvara Radnik_Informacije stol u Civilna_industrija Baza podataka:

 CREATE TABLE Worker_Info ( Worker_ID INT NOT NULL PRIMARY KEY, Worker_Name VARCHAR (100), Worker_Gender Varchar(20), Worker_Age INT NOT NULL DEFAULT 18, Worker_Address Varchar (80), Worker_Salary INT NOT NULL ); 

Korak 3: Umetnite vrijednosti

Sljedeći INSERT upiti umeću zapise radnika u tablicu Worker_Info:

 INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1001, Arush, Male, Agra, 35000); INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1002, Bulbul, Female, Lucknow, 42000); INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1004, Saurabh, Male, 20, Lucknow, 45000); INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1005, Shivani, Female, Agra, 28000); INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1006, Avinash, Male, 22, Delhi, 38000); INSERT INTO Worker_Info (Worker_ID, Worker_Name, Worker_Gender, Worker_Age, Worker_Address, Worker_Salary) VALUES (1007, Shyam, Male, Banglore, 20000); 

Korak 4: Pregledajte podatke tablice

Sljedeći upit prikazuje podatke tablice Worker_Info.

 SELECT * FROM Worker_Info; 

Korak 4: Koristite operator NOT IN

Sljedeći upit koristi NOT IN operator s numeričkim podacima:

 SELECT * FROM Worker_Info WHERE Worker_salary NOT IN (35000, 28000, 38000); 

Ovaj SELECT upit prikazuje sve one radnike u izlazu čija plaća nije proslijeđena u NOT IN operatoru.

Rezultat gornje izjave prikazan je u sljedećoj tablici:

Sljedeći upit koristi logički operator NOT IN sa znakovnim ili tekstualnim vrijednostima:

 SELECT * FROM Worker_Info WHERE Worker_Address NOT IN (Lucknow, Delhi); 

Ovaj upit prikazuje zapis svih onih radnika čija adresa nije proslijeđena u NOT IN operatoru.

Izlaz: