TechCodeview

I denne SQL-artikel lærer du, hvordan du bruger IN-nøgleord i SQL-databasens forespørgsler.

Hvad er IN nøgleord i SQL?

Det I er en logisk operator i Structured Query Language, der tillader databasebrugere at definere mere end én værdi i WHERE-sætningen.

WHERE-udtrykket med IN-operatoren viser de poster i resultatet, som matches med det givne sæt værdier. Vi kan også angive underforespørgslen i parentesen af ​​IN-operatoren.

Vi kan bruge IN-operatoren med INSERT-, SELECT-, UPDATE- og DELETE-forespørgslerne i SQL-databasen.

IN-operatoren i SQL erstatter processen med flere ELLER-betingelser i forespørgsler.

Syntaks for IN-operator:

 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); 

Hvis du vil bruge IN-operatoren i SQL-sætningerne, skal du følge nedenstående trin i samme rækkefølge:

1. Opret en database i SQL.
2. Opret den nye SQL-tabel.
3. Indsæt dataene i tabellen.
4. Se de indsatte data.
5. Brug SQL IN-operatoren til at vise tabellens data.

Nu skal vi kort forklare hvert trin et efter et med det bedste SQL-eksempel:

Trin 1: Opret en simpel ny database

Det første trin er at oprette en ny database i Structured Query Language.

Følgende CREATE-sætning opretter den nye Mekanisk_Høgskole Database i SQL server:

 CREATE Database Mechanical_College; 

Trin 2: Opret en ny tabel

Brug nu følgende SQL-syntaks, der hjælper med at oprette den nye tabel i databasen:

 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) ); 

Følgende CREATE-sætning opretter Fakultet_Info bord i Mekanisk_Høgskole Database:

 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 ); 

Trin 3: Indsæt dataene i tabellen

Følgende INSERT-forespørgsler indsætter fakulteternes optegnelser i Faculty_Info-tabellen:

 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); 

Trin 4: Se de indsatte data

Følgende SELECT-sætning viser dataene i Faculty_Info-tabellen:

 SELECT * FROM Faculty_Info; 

Trin 5: Brug IN-operatøren til at se dataene i Faculty_Info-tabellen på forskellige måder

Følgende forespørgsel bruger numeriske værdier med IN-operator:

 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); 

Denne forespørgsel viser kun registreringen af ​​de fakulteter, hvis løn er bestået i IN-operatøren af ​​WHERE-klausulen.

Produktion:

til string array java

Følgende forespørgsel bruger tekst- eller tegnværdier med logisk IN-operator:

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

Denne forespørgsel viser kun posten for de fakulteter, hvis by er inkluderet i parentesen af ​​IN-operatoren i WHERE-klausulen.

Produktion:

Følgende forespørgsel bruger DATEformat med IN logisk operator:

 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); 

Denne forespørgsel viser kun de optegnelser fra fakulteter, hvis tiltrædelsesdato er passeret i IN-operatoren for WHERE-klausulen.

Produktion:

Følgende forespørgsel bruger SQL UPDATE-kommandoen med logisk IN-operator:

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

Denne forespørgsel opdaterer lønnen for de fakulteter, hvis Dept_Id er bestået i IN-operatøren for WHERE-klausulen.

For at kontrollere resultatet af ovenstående forespørgsel skal du skrive følgende SELECT-forespørgsel i SQL:

 SELECT * FROM Faculty_Info; 

SQL IN-operatør med underforespørgsel

I Structured Query Language kan vi også bruge underforespørgslen med den logiske IN-operator.

Syntaksen for IN-operator med underforespørgsel er angivet nedenfor:

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

Hvis du vil forstå IN-operator med underforespørgsel, skal du oprette de to forskellige tabeller i Structured Query Language ved hjælp af CREATE-sætningen.

Følgende forespørgsel opretter Faculty_Info-tabellen i databasen:

 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 ); 

Følgende forespørgsel opretter Afdeling_Info tabel i databasen:

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

Følgende INSERT-forespørgsler indsætter fakulteternes optegnelser i Faculty_Info-tabellen:

 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); 

Følgende INSERT-forespørgsler indsætter posterne for afdelinger i tabellen Department_Info:

 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); 

Følgende SELECT-sætning viser dataene i Faculty_Info-tabellen:

 SELECT * FROM Faculty_Info; 

Følgende forespørgsel viser afdelingens poster fra tabellen Department_Info:

 SELECT * FROM Department_Info; 

Følgende forespørgsel bruger IN-operator med en underforespørgsel:

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

Denne forespørgsel viser posten for de fakulteter, hvis Dept_ID of Faculty_Info-tabellen matches med Dept_Id of Department_Info-tabellen.

Produktion:

Hvad er IKKE IN i SQL?

NOT IN er en anden operator i Structured Query Language, som er lige modsat SQL IN operatoren. Det giver dig adgang til disse værdier fra tabellen, som ikke passerer i parentesen af ​​IN-operatoren.

NOT IN-operatoren kan bruges i SQL-forespørgslerne INSERT, UPDATE, SELECT og DELETE.

Syntaks for NOT IN-operator:

 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); 

Hvis du vil bruge NOT IN-operatoren i SQL-sætningerne, skal du følge de givne trin i samme rækkefølge:

1. Opret en database i SQL-systemet.
2. Opret den nye tabel i databasen.
3. Indsæt data i tabellen
4. Se de indsatte data
5. Brug NOT IN-operatoren til at se dataene.

Nu skal vi kort forklare hvert trin et efter et med det bedste SQL-eksempel:

Trin 1: Opret den simple nye database

Følgende forespørgsel opretter den nye Civil_Industri Database i SQL server:

 CREATE Database Industry; 

Trin 2: Opret den nye tabel

Følgende forespørgsel opretter Worker_Info bord i Civil_Industri Database:

 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 ); 

Trin 3: Indsæt værdierne

Følgende INSERT-forespørgsler indsætter poster for arbejdere i Worker_Info-tabellen:

 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); 

Trin 4: Se tabellens data

Følgende forespørgsel viser dataene i tabellen Worker_Info.

 SELECT * FROM Worker_Info; 

Trin 4: Brug NOT IN-operatoren

Følgende forespørgsel bruger NOT IN-operatoren med numeriske data:

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

Denne SELECT-forespørgsel viser alle de arbejdere i outputtet, hvis løn ikke overføres i NOT IN-operatoren.

Resultatet af ovenstående udsagn er vist i følgende tabel:

Følgende forespørgsel bruger NOT IN logisk operator med tegn- eller tekstværdier:

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

Denne forespørgsel viser posten for alle de arbejdere, hvis adresse ikke er sendt i NOT IN-operatoren.

Produktion: