How to compare a column to multiple values in SQL?

Use the IN operator to efficiently compare columns with multiple values, such as SELECT*FROMemployeesWHEREdepartment_idIN(1,2,3); use NOTIN if multiple values ​​are excluded, but pay attention to NULL affecting the result.

Subqueries are queries nested in another query. They are suitable for simple one-time calculations and can be located in SELECT, FROM or WHERE clauses; 2. CTE is defined through WITH clauses to improve the readability of complex queries and support recursion and multiple references; 3. Subqueries are suitable for single use, and CTE is more suitable for scenarios where clear structure, reuse or recursion is required.

The SOUNDEX function converts text into a four-character code representing pronunciation, adds three digits to the first letter, ignores vowels and specific letters, and maps consonants with similar pronunciations to the same number, realizing pronunciation-based search. For example, Smith and Smythe both generate S530, and names with similar pronunciations can be found through WHERESOUNDEX(last_name)=SOUNDEX('Smith'). Combined with the DIFFERENCE function, it can return a similarity score of 0 to 4, filter the results of pronunciation close, which is suitable for dealing with spelling differences, but has limited effect on non-English names, and performance optimization needs to be paid attention to.

UseCOMMENTONCOLUMNorALTERTABLEwithCOMMENTtodocumenttablesandcolumnsinSQL;syntaxvariesbyDBMS—PostgreSQLandOracleuseCOMMENTON,MySQLusesCOMMENTinCREATE/ALTERstatements,andcommentscanbeviewedviasystemtableslikeINFORMATION_SCHEMA,butSQLitelackssupport.

Tofindorphanedrecords,useaLEFTJOINorNOTEXISTStoidentifychildrecordswithoutmatchingparentrecords.Forexample,SELECTo.FROMOrdersoLEFTJOINCustomerscONo.customer_id=c.customer_idWHEREc.customer_idISNULLreturnsorderslinkedtonon-existentcustomers.Alternativ

Anon-equijoinusescomparisonoperatorslikeBETWEEN,>=,or!=tomatchrowsbetweentables.2.Itisusefulforrangecomparisonssuchassalaryordateranges.3.SyntaxinvolvesspecifyingconditionsintheONclause,e.g.,usingBETWEENforsalarygrades.4.CanbeusedwithINNERJOIN,LEF

When using CREATETABLE, add UNIQUE keyword or use ALTERTABLEADDCONSTRAINT to add constraints to existing tables to ensure that the values ​​in the column are unique, and support single columns or multiple columns. Before adding, you need to ensure that the data is not duplicated. You can delete it through DROPCONSTRAINT, pay attention to the syntax differences between different databases and NULL values.

Use the LAST_DAY() function (MySQL, Oracle) to directly obtain the last day of the month where the specified date is, such as LAST_DAY('2023-10-15') to return 2023-10-31; 2. SQLServer uses the EOMONTH() function to achieve the same function; 3. PostgreSQL calculates the end of the month through DATE_TRUNC and INTERVAL; 4. SQLite uses the date function to combine 'startofmonth', '1month' and '-1day' to obtain the results.