ell Notes for Professionals 100+ pages of professional hints and tricks , Prerincs GoalKicker.com ae ae te cen et Free Programming Books alPircdlemarksandresisered wademarkears ree gia earsContents About hapter 1: Getting started with SQL Section 11: Overview Chapter 2: Identifier Section 21: Unguoted identifiers Chapter 3: Data Types 31: DECIMAL and NUMERIC ‘Section 32 FLOAT and REAL Section 33: Integers Section 3.4 MONEY and SMALLMONEY. ‘Section 351 BINARY and VARBINARY Section 3.6: CHAR and VARCHAR Section 37: NCHAR ond NVARCHAR t JOU! iE Chapter 4: NULL 7 tion 41 Filtering for NULL in quer Sect ble: Section 4: Updating fields to NULL ‘Section 4.4: Inserting rows with NULL fields .. Chapter 5: Example Databases and Tables .. Section 5.1 Auto Shop Datobase Section 5.2: Librory Database Section 53: Countries Table Chapter 6: SELECT jection 61 Using the wildcard character to select all columns in ¢ quer 42: Nullgble columns SNADMAAUHHEARDRARRWYNN Section 62: SFLECT Using Column Aliases 15 Section 63: Select Individuel COIUMNS nmin 18 lon 64: Select fled number of recor 19 Section 65: Selecting with Conaition .... 20 Section 66: Selecting with CAS: a se o a se 20 0 ned after reser fa o snsnnn 21 tion 6.8: Selecting with table alas 2 Section 6 Selecting with more than 1 condition 22 on 61 ting without Locking sininnnninnninnininininonninnnnnnnnnnnnne 23 Section 61%: Selecting with Aggregate fUNCHIONS -rinnnmminmnininenimenininnnnnannnnenmnenesennies 2B Section 612: Select with condition of multiple values from column 24 Section 613: Get agareqated resull for row groups 24 Section 614: Selection with sorted Results . Section 6.15: Selecting with null Section 616: Select distinct (unique values onl 28 Section 617: Select rows from multiple tables snc sn 26 Chapter 7: GROUP BY ....sonnnninmniinninnninnininininnninininennnnsnnn seonsnnnsnnnnine OT Section 71: Basic GROUP BY example 7 Section 7.2 Filter GROUP BY results using 6 HAVING clause 28 Section 75: USE GROUP BY to COUNT the number of rows for each ynique entry in a given columa 28 Section 7.4: ROLAP agaregotion (Data Mining) 29Chapter 8: ORDER BY Section 81: Sorting bu column number (instead of name) with TOP to retur rows based Section 83 Customized sorting order Section 84: Order by Alios Chapter 9: AND & OR Operators ‘Section 9:1 AND OR Example Chapter 10: CASE .. Section 101 n 103: CASE sion 10.4: Shorthan¢ tlon 107: CASE in Chapter 11: LIKE operator IRDER BY cla ort tion 111 Motch open. Section 112: Single character match Section 113: ESCAPE statement in the LIKE-qven Section 114: Search for @ range of characters Section 115: Match by range or set Section 116: Wildcard characters Chapter 12: IN clause on 121: Simpl .¢ CASE to COUNT the num Section 102: Seorched CASE in SELECT (Matches o boolean expression) in clause ORDER BY AS Ein SELECT. lowest valu tern Section 12.2: Using IN clause with @ SUBQUEEY warn Chapter 13: Filter results using WHERE and HAVING Sect ti WHERE ch Section 13.4: Equal 1 Use BETWEEN to Filter Results Section 15.2: Use HAVING with Aggregate Functions with NOT NULL ve ch a cor Section 135: The WHERE clause only returns rows that mateh its erterig ‘Section 136: AND and OR Section 157: Use IN to rei ‘ows with @ value contained in ¢ list Section 138 Use LIKE to find matching strings and substrings 0 Zh Section 1510: Use HAVING o check for multiple conditions in a Chapter 14: SKIP TAKE (Pagination) Section 14. Limiting amount of results Section 142: Skipping then toking some results (Pagination) section 143: Skipping some rows {rom result Chapter 15: EXCEPT Section 151i Select dataset exce where values ore Chapter 16: EXPLAIN and DESCRIBE .. Section 16. EXPLAIN Select quer Section 162: DESCRIBE toblenaMe ran Chapter 17: EXISTS CLAUSE on 171.6 Chapter 18: JOIN 3 a 31 32 32 33 3 35, 38 36 36 36 37 37 38 39 39 40 40 41 a a 43 44 45, 45, 46 ss 6 46 a7 a7 AB. 43. 50 50 vs 50 51 52 52 on 53 53 53 54 55.Section 18.1: Self JOIN nmin stninnnininnnnninineniinininnnnnnnenninnenns 5S Section 182: Differences between inner/outer joins 56 Section 183: JOIN Terminologur Inner, Outer. Semi, Anti 59 Section 18.4: Left Outer Join see 69 ‘Section 185: Implicit Join . vn 70 Section 186: CROSS JOIN n Section 187: CROSS APPLY & LATERAL JOIN. eon T2 on 18.8: ft son ' oe 7B Section 189: Recursive JOINS a . sen se sens TA Section 18,10; Basic explicit Inner IOI neu se sa scninnnnninnnnenne TA lon 18.1" Joining on a 75, Chapter 19: UPDATE se 76 ction 195% UPDATE with data from another tab! 76 Section 19,2: Modifying existing valves, 7 Section 193: Undating Specified Rows .. ‘Section 19.4: Updating All Rows .. . sen so 7 7 Section 19.5: Copturing Undated record's 7 Chapter 20: CREATE Database ........ ss se se se 78 Section 20.1 CREATE Database 78 Chapter 21: CREATE TABLE 79 Section 211: Create Table From Select suo 79 on 212: Cr we sn sen ee 7 79 Section 215: CREATE TABLE With FOREIGN KEY. .sironnnnnnn seni 73 lon 215: Create o Temporary or In-Memory TabIE snes smninsnsnninnninennsnennsnsnne 80 Chapter 22: CREATE FUNCTION ... se o ss 82 ‘Saction 22:1 Create a new Function 82 Chapter 23: TRY/CATCH .csininninnnnnininininmininininnnnninnnnnininmninnnnnnnnnnnnnen 8B Section 231: Transaction in a TRY/CATC 83 ster 24: UNION / UNION ALL Section 241: Bosic UNION ALL quer 84 85 Chapter 25: ALTER TABLE Section 251: Add Column(s) Section 25.2: Drop Column Section 25.3: Add Primary Ke Section 254: Alter Colun Chapter 26: INSERT .. sn vo Section 261: INSERT data from another table using SELECT Section 262 Insert New Row .. Section 264: Insert multiple cows at once Chapter 27: MERGE ....... o Section 27.1 MERGE to make Target match Sour Section 272: MySQL: counting users by name ... jection 27°3 PostgreSOl: counting users by nam Chapter 28: cross apply, outer apply Section 28.1 CROSS APPLY and QUTER APPLY basics...Chapter 29: DELETE 22 291: DELETE all rows 92 9. iain rows with W sn 92 293 TRUNCATE clause 92 Section 294: DELETE certain rows bosed upon comparisons with other tables 92 Chapter 30: TRUNCATE 94 Section 30.1: Removing all rows from th: nnn vanes 94 Chapter 31: DROP Table 95, section 311: Check for existence before dronpin: sen son smnennnnnne 95 ‘Section 312 Simple drop 95, Chapter 32: DROP or DELETE Database .......c0n o a o 96 Sect 96 hapter 33: a — a 7 Section 351: ON DELETE CASCADE 7 Chapter 34: GRANT and REVOKE 9 Section 341 Grant/revoke privileges 99 Chapter 35: XML 100 Section 35.1 Query from XML Dota Type -uinmnnnninmininininuiinninnnnnninnnmniennnnnnnnn 100 Chapter 36: Primary Keys 101 action 36%: Creating @ PAMAPY KEY nnsnnninmninmininenimnnnninminnninennnnnmnnnnnnnnnnns 101 Section 362: Using Auto Increment 101 Chapter 37: Indexes 102 Section 371 Sorted index 102 Section 3, 1102 jection 375. Creating dA INGEX nimmnmnmnmninmenininnnsnnannnanineninennmnnnnnnnanineneninenrnennne 102 Section 37.4: Dropping on Index or Disabling and Rebuilding it 103 Section 375: Clustered, Unique, and Sorted Indexes 103 ‘Saction 32.6 Rebulld index smn ve se 104 Section 377: Inserting with 0 Unique Index 104 Chapter 38: Row number seven 105 Section 38.1 Delete All But Lost Record (1 to Many Table) 105 Section 382: Row numbers without partitions ssiinminminininineninimninnninnnnmnninennennnnsnnnnn 105 Section 38.3: Row numbers with partitions snes smn 105 Chapter 39: SOL Group By vs Distinct 106 Section 39.1 Difference between Gi 106 Chapter 40: Finding Duplicates on a Column Subset with Detail 107 Section 40 St with some name and dote of birth 107 Chapter 41: String Functions 108 Section 411: Concatenat 108 Section 412: Lenat! sinnnnninniinniiiinnninninnnnnininninnnnnnnnnennnnnns 108 Section 413: Trim empty spaces. svar . 109 Section 414: Upper & lower case 109 Section 415: Split rnin o oe sannnnnnenenenrneesee 109 Section 416 Replace 110 417. REGEXP 110 Section 418: Substri 110 SOCIO 419: SUE sasnmninmenninnninininmninnnenennsenn st — senninesnnen VIO Section 410: LEFT - RIGHT 110REVERSE REPLICATE Section 4115: PARSENAME sso Chapter 42: Functions (Aggregate) 422: Uist Concatenation 423. SUM Section 42.4 AVGO Section 425: Cour Se sclion 42.6 Min Section 427: Max. Chapter 43: Functions (Scalar/Single Row) .. 4511 Date And Time 43.2: Character Section 433: Configuration and Conversion Function .. Section 43.4: Logical and Mathmetical Funct Chapter 44: Functions (Analutic) Section 441: LAG and LEAD Section 442: PERCENTILE DISC ond PERCENTILE CONT Section 443: FIRST VALUE Section 444 LAST VALUE 445: PERCENT RANK and CUME DIST Sect Chapter 45: Window Functions fications her rows have a commen py vence" records using the LAGO function Section 45.3: Getting o running total Section 45.4: Adding the total rows selected to every row os Section 4555: Getting the N most recent rows over multiple grouping Chapter 46: Common Table Expressions Section 46.1 generating values Section 46.2 recursively enumerating a subtree Section 463: Temporary quer Section 46.4: recursively going up IN BOE . . vo Section 46.5: Recursively generate dates. extended to include team rostering as example Section 46.6: Oracle CONNECT BY functionality with recursive CTES Chapter 47: Views Section 4711 Simple views .. Section 47.2: Complex views hapter 48: Materialized View: Section 48.1: PostoreSOL example Chapter 49: Comments Section 4911 Single-line comments Chapter 50: Foreign Keys .. Section 50.1 Foreign Keys explainec Section 50,2: Creating a table with a foreign ke Chapter 51: Sequence m1 m1 1 2 2 114 114 14 116 116 116 M7 8 119 19 120 120 121 123 123 123, vo 124 125 125 127 127 127 128 128 129 130 130 130 131 131 132 132 134 134 134 135 135 136 136 136 137 137 137 139Section 511: Create Sequence Section 512: Using Sequences Chapter 52: Subaveries . . Section 521: Subquery in FROM clause Section 52.2: Subquery in SELECT clause section 5; very in WH Section 52.6: Subqueries in FROM clause .. Section 52.7: Subqueries in WHERE clause hapter 53: Execution block: Section 531: Using BEGIN. END Chapter 54: Stored Procedures in" for Chapter 56: Transactions .... jection 5611 Simple Transaction Sectio Chapter 57: Table Design Section 57.1 Properties of a well designed table Chapter 58: Synonyms Section 58.1 Creote Synonym . Chapter 59: Information Schema Section 59.1 Basic Information Schema Seore! Chapter 60: Order of Execution section 601 Logical Order of Query Processing in SOL Chapter 61: Clean Code in SQL sn Section 61: Formatting and Spelling of Keywords and Names Section 612: indenting n 613: SELECT" 562: Rollback Transaction Section 61,4: Join se Chapter 62: SOL Injection ... Section 621: SO injection sample Section 62.2: simple injection somph Credits You may also like 139 139 140 140 140 140 140 140 141 1a 142 142 143 143 144 144 14a 145 145 145 146 146 147 147 148 148 149 149) 150 150 150 151 152 153 153 154 155 159About Please feel free to share this PDF with anyone for free, latest version of this book can be downloaded from: https://goalkicker.com/SQLBook This SQL Notes for Professionals book is compiled from Stack Overflow Documentation, the content is written by the beautiful people at Stack Overflow. Text content is released under Creative Commons BY-SA, see credits at the end of this book whom contributed to the various chapters. Images may be copyright of their respective owners unless otherwise specified This is an unofficial free book created for educational purposes and is not affiliated with official SQL group(s) or company(s) nor Stack Overflow. All trademarks and registered trademarks are the property of their respective company owners ‘The information presented in this book is not guaranteed to be correct nor accurate, use at your own risk Please send feedback and corrections to web@veterev.com Goolkicker-com - SQL Notes for Professionals,Chapter 1: Getting started with SQL Version Short Name Standard Release Date 1986 — SQL-86 ANSI X3.135-1986, ISO 9075:1987, 1986-01-01 1989 SQL-89 ANSI X3,135-1989, ISO/IEC 9075:1989 1989-01-01 1992 SQL-92 ISOMEC 9075:1992 1992-01-01 1999 $QL:1999— ISOMEC 9075:1999 1999-12-16 2003 $QL:2003_ISOMEC 9075:2003 2003-12-15 2005 SQL2006 ISO/IEC 9075:2006, 2006-06-01 (2008 = $QL:2008 ~— ISOMEC 9075:2008 2008-07-15, 2011 sQu2011_IsONEC 9075:2011 2011-12415 2016 SQL2016 ISO/EC 9075:2015, 2016«12-01 Section 1.1: Overview Structured Query Language (SQL) is a special-purpose programming language designed for managing data held in a Relational Database Management System (RDBMS). SQL-like languages can also be used in Relational Data Stream Management Systems (RDSMS), or in "not-only SQL” (NoSQL) databases. ‘SQL comprises of 3 major sub-languages 1. Data Definition Language (DDL): to create and modify the structure of the database; 2. Data Manipulation Language (DML): to perform Read, Insert, Update and Delete operations on the data of the database; 3. Data Control Language (DCL): to control the access of the data stored in the database. SOL article on Wikipedia ‘The core DML operations are Create, Read, Update and Delete (CRUD for short) which are performed by the statenents INSERT, SELECT, UPDATE and DELETE. There is also a (recently added) MERGE statement which can perform all 3 write operations (INSERT, UPDATE, DELETE), CRUD article on Wikipedia Many SQL databases are implemented as client/server systems; the term "SQL server" describes such a database. ‘At the same time, Microsoft makes a database that is named "SQL Server". While that database speaks a dialect of SQL. information specific to that database is not on topic in this tag but belongs into the SQL Server documentation, Goolkicker-com - SQL Notes for Professionals, 2Chapter 2: Identifier ‘This topic is about identifiers, ie. syntax rules for names of tables, columns, and other database objects. Where appropriate, the examples should cover variations used by different SQL implementations, or identify the ‘SQL implementation of the example. Section 2.1: Unquoted identifiers Unquoted identifiers can use leters (a2), digits (28), and underscore (.), and must start with a letter Depending on SQL implementation, and/or database settings, other characters may be allowed, some even as the first character, e.g. MS SQL: 8 §, #, and other Unicode letters (source MySQL: $ (source) Oracle: §, #, and other letters from database character set (source PostgreSQL. §, and other Unicode letters (source) Unquoted identifiers are case-insensitive. How this is handled depends greatly on SQL implementation: + MS SQL: Case-preserving, sensitivity defined by database character set, so can be case-sensitive MySQL: Case-preserving, sensitivity depends on database setting and underlying file system. Oracle: Converted to uppercase, then handled like quoted identifier. PostgreSQL: Converted to lowercase, then handled like quoted identifier SQLite: Case-preserving; case insensitivity only for ASCII characters. Goolkicker-com - SQL Notes for Professionals,Chapter 3: Data Types Section 3.1: DECIMAL and NUMERIC Fixed precision and scale decimal numbers. DECIMAL. and NUNERIC are functionally equivalent. Syntax: DECIMAL ( precision | , scale NUMERIC ( precision | | scale Examples: SELECT CAST(123. AS DECIMAL(S,2)) --returns 123.00 SELECT CAST(12345.12 AS NUMERIC(1®, 5)) --returns 12345.12000 Section 3.2: FLOAT and REAL Approximate-number data types for use with floating point numeric data, SELECT CAST( PI() AS FLOAT) --returns 3.14159265358079 SELECT CAST( PI() AS REAL) ~-returns 9.141893, Section 3.3: Integers Exact-number data types that use integer data. Data type Range Storage bigint —-2%63 (-9,223,372,036,854,775,808) to 2%63-1 (9,223,372,036,854,775,807) 8 Bytes int 2931 (2,147 483,648) to 2931-1 (2,147,483,647) 4 Bytes smallint -215 (-32,768) to 2415-1 (32,767) 2 Bytes tinyint 0 t0 255, 1 Byte Section 3.4: MONEY and SMALLMONEY Data types that represent monetary or currency values, Data type Range Storage money _-922,337,203,685,477.5808 to 922,337,203,685,477.5807 & bytes smallmoney -214,748,3648 to 214,748.3647 bytes Section 3.5: BINARY and VARBINARY Binary data types of either fixed length or variable length, syntax: BINARY [ ( nbytes ) VARBINARY [ ( nbytes | max ) ] n_hytes can be any number from 1 to 8000 bytes, max indicates that the maximum storage space Is 2431-1 Goolkicker-com - SQL Notes for Professionals,Examples: SELECT CAST(12345 AS BINARY(1@)) -- exoeaaee0e000008003039 SELECT CAST(12345 AS VARBINARY(16)) -- @x00803039 Section 3.6: CHAR and VARCHAR String data types of either fixed length or variable length. syntax: CHAR [ ( nchars ) | VARCHAR [ ( nchars ) | Examples: SELECT CAST(*ABC' AS CHAR(10)) -- ‘ABC (padded with spaces on the right) SELECT CAST("ABC' AS VARCHAR(1@)) ~~ ‘ABC’ (no padding due to variable character) SELECT CAST(*ABCOEFGHIJKLNNOPORSTUVWKYZ' AS CHAR(1)) -- ‘ABCDEFGHIJ' (truncated to 1@ characters) Section 3.7: NCHAR and NVARCHAR UNICODE string data types of either fixed length or variable length, syntax: NCHAR [ ( nchars ) NVARCHAR [ ( nchars | MAX ) Use WX for very long strings that may exceed 8000 characters. Section 3.8: UNIQUEIDENTIFIER A 16-byte GUID / UID. DECLARE @GUID UNTQUEIDENTIFIER = NEWID() SELECT @GUID -- "E28B3BD9-9174~41A9-B508-B99A78A39540" DECLARE @bad_GUTD_string VARCHAR(100) = ‘£2889BD9-9174-41A9-8508-899A78A33540_foobarbaz SELECT @bad_GUID_string, ~~ '£2863BD9-9174-41A9-8508-899A78A33540_foobarbaz CONVERT (UNIQUEIDENTIFIER, @bad_GUID_string) -~ 'E28B3BD9-9174-41A9-8508-899A7EAI9540 Goolkicker-com - SQL Notes for Professionals, 5Chapter 4: NULL NULL in SQL, as well as programming in general, means literally "nothing". In SQL, itis easier to understand as “the absence of any value" Itis important to distinguish it from seemingly empty values, such as the empty string ** or the number 8, neither ‘of which are actually NULL. Itis also important to be careful not to enclose NULL in quotes, like "NULL’, which is allowed in columns that accept text, but is not NULL and can cause errors and incorrect data sets Section 4.1: Filtering for NULL in queries The syntax for filtering for NULL (ie. the absence of a value) in WHERE blocks is slightly different than filtering for specific values. SELECT * FROM Employees WHERE ManagerTd IS NULL SELECT * FROM Employees WHERE ManagerId IS NOT NULL Note that because NULL is not equal to anything, not even to itself, using equality operators = NULL or <> NULL (or = NULL) will always yield the truth value of UNKNOWN which will be rejected by WHERE. WHERE fters all rows that the condition is FALSE or UKNOWN and keeps only rows that the condition is TRUE. Section 4.2: Nullable columns in tables When creating tables itis possible to declare a column as nullable or non-nullable. CREATE TABLE MyTable - non=nullable nullable MyCol1 INT NOT NULL, MyCol2 INT NULL By default every column (except those in primary key constraint) is nullable unless we explicitly set NOT NULL constraint, Atternpting to assign NUL to a non-nullable column will result in an error. INSERT INTO MyTable (MyCol1, MyCol2) VALUES (1, NULL) ; ~~ works fine INSERT INTO MyTable (MyCol1, MyCol2) VALUES (NULL, 2) cannot inse the value NULL into column ‘MyCol1', table ‘MyTable column does not allow nulls. INSERT fails. Section 4.3: Updating fields to NULL Setting afield to NULL works exactly lke with any other value: UPDATE Employees SET ManagerId = NULL WHERE Td = 4 Goolkicker-com - SQL Notes for Professionals,Section 4.4: Inserting rows with NULL fields For example inserting an employee with no phone number and no manager into the Employees example table: INSERT INTO Employees (Id, FName, Lame, PhoneNunber, MenagerId, DepartmentId, Salary, HireDate) VALUES (5, ‘Jane’, ‘Doe’, NULL, NULL, 2, 888, '2016-07-22") Goolkicker-com - SQL Notes for Professionals,Chapter 5: Example Databases and Tables Section 5.1: Auto Shop Database In the following example - Database for an auto shop business, we have a list of departments, employees, ‘customers and customer cars, We are using foreign keys to create relationships between the various tables. Live example: SOL fiddle Relationships between tables ‘+ Each Department may have 0 or more Employees ‘+ Each Employee may have 0 or 1 Manager * Each Customer may have 0 or more Cars Departments Id Name 1 HR 2 Sales 3 Tech ‘SQL statements to create the table: CREATE TABLE Departments ( Td INT NOT NULL AUTO_INCREMENT, Name VARCHAR(25) NOT NULL. PRIMARY KEV(Td) INSERT INTO Departments [14], [Name] VALUES 1, CHR) (2, ‘sales') (3, ‘Tech’ Employees IdFName LName PhoneNumber Managerld Departmentid Salary HireDate 1 James Smith 1234567890 NULL 1 1000 01-01-2002 2 John Johnson 24681012141 1 400. 23-03-2005, 3 Michael Williams 13579111311 2 600 12-05-2009 4 Johnathon Smith 12121212122 1 500 24-07-2016 ‘SQL statements to create the table: CREATE TABLE Employees ( Id INT NOT NULL AUTO_INCREMENT, FName VARCHAR(35) NOT NULL, LName VARCHAR(35) NOT NULL, PhoneNunber VARCHAR(11) Managertd INT DepartmentId INT NOT NULL, Goolkicker-com - SQL Notes for Professionals,Salary INT NOT NULL HireDate DATETIME NOT NULL PRIMARY KEY(Id) FOREIGN KEY (NanagerId) REFERENCES Employees(Id) FOREIGN KEY (DepartmentId) REFERENCES Departments (Id) INSERT INTO Enployees ((1d), [FName), [LName), [PhoneNunber), [ManagerId), [Departmentid), (Salary VALUES (1, ‘James", ‘Smith’, 1234567890, NULL, 1, 1808, '@1-01-2002") 2, ‘John’, ‘Johnson’, 2468101214, '1', 1, 408, '23-03-2605') (3, ‘Michael’, ‘Willians’, 1957911191, °1°, 2, 608, '12-@5-2009") 4, ‘Johnathon’, ‘Smith, 1212121212, '2', 1, 588, '24-07-2016") Customers Id FName LName Email PhoneNumber PreferredContact 1 William Jones william jones@example.com 3347927472 PHONE 2 David Miller dmiller@example.net 2137921892 EMAIL 3 Richard Davis richard0123@example.com NULL EMAIL ‘SQL statements to create the table: CREATE TABLE Customers ( Id INT NOT NULL AUTO_INCREMENT, FName VARCHAR(35) NOT NULL. UName VARCHAR(35) NOT NULL. Email verchar(108) NOT NULL. PhoneNunber VARCHAR(11) PreferredContact VARCHAR(5) NOT NULL. PRIMARY KEY(Id) INSERT INTO Customers ((Td], [FName}, (LName), [Email], [PhoneNunber}, [PreferredContact]) VALUES (1, ‘Willian’, "Jones", ‘william. jones@example.com’, "3347927472", "PHONE 2. ‘David’, ‘Miller’, ‘dmillergexample.net', '2137921892', ' EMATL’ (3, ‘Richard’, ‘Davis’, ‘richard®1238exanple.com’, NULL, ‘EMAIL’ cars Id Customerld Employeeld Model Status Total Cost 4 2 FordF-150 READY 230 21 2 FordF-150 READY 200 32 1 Ford Mustang WAITING 100 43 3 Toyota Prius WORKING 1254 ‘SQL statements to create the table: CREATE TABLE Cars ( Td INT NOT NULL AUTO_INCREMENT, CustomerId INT NOT NULL Enployeetd INT NOT NULL Model verchar(5@) NOT NULL Status varchar(25) NOT NULL HireDate)) Goolkicker-com - SQL Notes for Professionals,TotalCost INT NOT NULL PRIMARY KEY(Id) FOREIGN KEY (CustomerId) REFERENCES Customers (Id FOREIGN KEY (EmployeeId) REFERENCES Enployees(Id) INSERT INTO Cars [Id], [Customertd], [Employed], [Model], [Status], [TotalCost)) VALUES 15, (11, '2', "Ford F-158", 'READY', '238") 2° 1", 12) "Ford F-158", "READY’, '208" 3°, '2', ‘1, ‘Ford Mustang’, ‘WAITING’, '160") (14", °3", 13") "Toyota Prius’, "WORKING, '1254") Section 5.2: Library Database In this example database fora library, we have Authors, Books and BooksAuthors tables. Live example: SOL fiddle Authors and Books are known as base tables, since they contain column definition and dats for the actual entities in the relational model. BooksAuthors is known as the relationship table, since this table defines the relationship between the Books and Authors table. Relationships between tables ‘+ Each author can have 1 or more books * Each book can have 1 or more aut! Authors (View table) IdName Country 1 J.D.Salinger USA 2 F. Scott. Fitzgerald USA 3 Jane Austen UK 4 Scott Hanselman USA 5 Jason N. Gaylord USA 6 Pranav Rastogi India 7 Todd Miranda USA 8 Christian Wenz USA SQL to create the table: CREATE TABLE Authors Id INT NOT NULL AUTO_INCREMENT, Name VARCHAR(7@) NOT NULL. Country VARCHAR(1@@) NOT NULL. PRIMARY KEY(Id) INSERT INTO Authors Goolkicker-com - SQL Notes for Professionals, 70(Name, Country) VALUES (J.D. Salinger’, “USA') °F, Scott. Fitzgerald’, ‘USA “Jane Austen’, "UK") “Scott Hanselman’, 'USA’) “Jason N. Gaylord’, “USA') “Pranay Rastogi’, ‘India') “Todd Miranda’, ‘USA’ “christian Wenz’, ‘USA') Books laTitle The Catcher in the Rye Nine Stories Franny and Zooey Tender id the Night Pride and Prejudice Professional ASP.NET 4.5 in C# and VB. 1 2 3 4 The Great Gatsby 5 6 7 SQL to create the table: CREATE TABLE Books ( Id INT NOT NULL AUTO_INCREMENT. Title VARCHAR(S®) NOT NULL PRIMARY KEY (Id) INSERT INTO Books (Id, Title) VALUES qa “The Catcher in the Rye") 2, ‘Nine Stories’) (3, ‘Franny and Zooey’) (4, ‘The Great Gatsby") (S. ‘Tender id the Night") (6, ‘Pride and Prejudice’), (7, "Professional ASP.NET 4.5 in C# and VB") BooksAuthors (view table) Bookld Authorid 1 ween 1 Goolkicker-com - SQL Notes for Professionals,evaune SQL to create the table: CREATE TABLE BooksAuthors ( ‘Authorid INT NOT NULL BookId INT NOT NULL. FOREIGN KEY (AuthorId) REFERENCES Authors(Id) FOREIGN KEY (BookId) REFERENCES Books( Id) INSERT INTO BooksAuthors (BookId, Authorid) VALUES (1) (2.1) 3.1) (4 2) 5, 2) (6 3) 7.4) 78 7. 6) @. 7 Examples View all authors (view live example): SELECT * FROM Authors: View all book titles (view live example): SELECT * FROM Books View all books and their authors (view live example! SELECT ba. Authorid @.Neme AuthorNane, ba. BookId: b.Title BookTitle FROM BooksAuthors ba INNER JOIN Authors @ ON a.id = ba.authorid INNER JOIN Books b ON b.id = ba.bookid Goolkicker-com - SQL Notes for Professionals,Section 5.3: Countries Table In this example, we have a Countries table. A table for countries has many uses, especially in Financial applications involving currencies and exchange rates. Live example: SOL fiddle ‘Some Market data software applications like Bloomberg and Reuters require you to give their API either a 2 or 3 character country code along with the currency cade. Hence this example table has bath the 2-character 180 code column and the 3 character 1803 code columns. Countries view table) Id IS0 1SO3 ISONumeric CountryName Capital __ContinentCode CurrencyCode 1 AU AUS 36 Australia Canberra. OC AUD 2 DE DEU 276 Germany Berlin. EU EUR 2 IN IND 356 India New Delhi AS INR 3 LA LAO 418 Laos Vientiane AS LAK 4 US USA 840 United States Washington NA usp 5 ZW 2WE 716, Zimbabwe Harare. A Ze SQL to create the table: CREATE TABLE Countries ( Id INT NOT NULL AUTO_INCREMENT, TSO VARCHAR(2) NOT NULL 1803 VARCHAR(3) NOT NULL Ts0Numerie INT NOT NULL CountryName VARCHAR(64) NOT NULL. Capital VARCHAR(64) NOT NULL, ContinentCode VARCHAR(2) NOT NULL CurrencyCode VARCHAR(3) NOT NULL, PRIMARY KEY(Td) INSERT INTO Countries (180, 1803, ISONumeric, CountryName, Capital, ContinentCode, CurrencyCode) VALUES (AU', "AUS", 96, “Australia’, ‘Canberra’, ‘Oc’, “AUD') DE", "DEU', 276, ‘Germany’, ‘Berlin’, "EU', ‘EUR') COIN", "IND", 956, "India", ‘New Delhi", “AS, "INR") LA", "LAO", 418, ‘Laos’, ‘Vientiane’, 'AS', ‘LAK') (US", "USA", 849, ‘United States’, ‘Washington’, ‘NA’, "USD" ZW’, '7WE'| 716, ‘Zimbabwe’, ‘Harare’, "AF", ‘ZWL" Goolkicker-com - SQL Notes for Professionals, 3Chapter 6: SELECT ‘The SELECT statement is at the heart f most SQL queries. It defines what result set should be returned by the query, and is almost always used in conjunction with the FROM clause, which defines what part(s) of the database should be queried, Section 6.1: Using the wildcard character to select all columns in a query Consider a database with the following two tables. Employees tabl Id FName LName Deptid 1 James smith 3 2 John Johnson 4 Departments table: ld Name 1 Sales 2 Marketing 3 Finance 4a ‘Simple select statement sisthe {card character used to select all available columns in a table, When used as a substitute for explicit column names, it returns all columns in all tables that a query is selecting, FROM, This effect applies to all tables the query accesses through its JOIN clauses, Consider the following query: SELECT * FROM Employees It will return all fields of all rows of the Employees table: Id FName LName Deptid 1 James smith 3 2 John Johnson 4 Dot notation To select all values from a specific table, the wildcard character can be applied to the table with dot notation, Consider the following query: SELECT Employees. Departments. Nane FROM Enployees JOIN Goolkicker-com - SQL Notes for Professionals,Departments ON Departments. Id = Enployees.Deptid This will return a data set with all fields on the Employee table, followed by just the Nane field in the Departments table: Id FName LName Deptid Name 1 James smith 3 Finance 2 John Johnson4 IT Warnings Against Use Itis generally advised that using + is avoided in production code where possible, as it can cause a number of potential problems including: 1. Excess 10, network load, memory use, and so on, due to the database engine reading data that is not needed and transmitting it to the front-end code. This is particularly a concern where there might be large fields such as those used to stare long notes or attached files. 2. Further excess 10 load if the database needs to spool internal results to disk as part of the processing for a query more complex than SELECT FROM . 3. Extra processing (and/or even more 10) if some of the unneeded columns are: © computed columns in databases that support them @ in the case of selecting from a view, columns from a table/view that the query optimiser could otherwise optimise out 4, The potential for unexpected errors if columns are added to tables and views later that results ambiguous column names. For example SELECT = FROM orders JOIN people ON people.id = orders.personid ORDER BY displayname - if a column column called di splaynane is added to the orders table to allow users to give their orders meaningful names for future reference then the column name will appear twice in the output so the ORDER BY clause will be ambiguous which may cause errors ("ambiguous column name” in recent MS SQL Server versions), and if not in this example your application code might start displaying the order narne where the person name is intended because the new column is the first of that name returned, and so on. When Can You Use #, Bearing The Above Warning In Mind? While best avoided in production code, using «is fine as a shorthand when performing manual queries against the database for investigation or prototype work, ‘Sometimes design decisions in your application make it unavoidable (in such circumstances, prefer tablealias.* ‘over just « where possible}, When using EXISTS, such as SELECT A.col1, A.Col2 FRON A WHERE EXISTS (SELECT * FROM B where A.ID B.A_TD), we are not returning any data from B. Thus a join is unnecessary, and the engine knows no values from B are to be returned, thus no performance hit for using «. Similarly COUNT() is fine as it also doesn’t actually return any of the columns, so only needs to read and process those that are used for filtering purposes. Section 6.2: SELECT Using Column Aliases Column aliases are used mainly to shorten code and make column names more readable. Code becomes shorter as long table names and unnecessary identification of columns (eg. there may be 2 JDs in the table, but only one is used in the statement) can be avoided. Along with table aliases this allows you to use longer descriptive names in your database structure while keeping queries upon that structure concise. Furthermore they are sometimes required, for instance in views, in order to name computed outputs. Goolkicker-com - SQL Notes for Professionals, ®All versions of SQL Aliases can be created in all versions of SQL using double quotes ("). SELECT FName AS “First Name” Wane AS "Middle Name’ LName AS “Last Name" FROM Employees Different Versions of SQL. You can use single quotes (’), double quotes (") and square brackets ([]) to create an alias in Microsoft SQL Server. SELECT FName AS “First Name” WWame AS “Middle Name LName AS [Last Name FROM Employees Both will result in: First Name Middle Name Last Name James John. ‘Smith John James Johnson Michael Marcus Williams This statement will return FName and LName columns with a given name (an alias). This is achieved using the AS ‘operator followed by the alias, or simply writing alias directly after the column name, This means that the following, query has the samme outcome as the above. SELECT FName “Firet Name” Wane “Middle Name LName “Last Name’ FROM Employees First Name Middle Name Last Name James John. Smith John James Johnson Michael Marcus Williams However, the explicit version (ie., using the AS operator) is more readable. Ifthe alias has a single word that is nota reserved word, we can write it without single quotes, double quotes or brackets: SELECT FName AS FirstName LName AS LastName FROM Employees FirstName LastName James Smith John Johnson Michael Williams Goolkicker-com - SQL Notes for Professionals, 6A further variation available in MS SQL Server amongst others is = , for instance: SELECT FullNane = FirstNane +‘ * + LastName Addrl = FullStreetAddress. Addr2 = TownNane FROM CustomerDetails which is equivalent to: SELECT FirstName + ° ' + LastName As FullNane FullStreetAddress. As Addr Townliane As Addr2 FROM CustomerDetails Both will result in: FullName Addr Addr2 James Smith 123 AnyStreet_ Townville John Johnson 668 MyRoad Anytown Michael Williams 999 High End Dr Williamsburgh ‘Some find using = instead of As easier to read, though many recommend against this format, mainly because itis not standard so not widely supported by all databases. It may cause confusion with other uses of the = character. All Versions of SQL Also, f you need to use reserved words, you can use brackets or quotes to escape: SELECT FName as *SELEC WName as “FROM” LName as “WHERE FROM Employees Different Versions of SQL. Likewise, you can escape keywords in MSSQL with all different approaches: SELECT FName AS “SELECT Name AS ° FROM! Lame AS [HEI FROM Employees SELECT FROM WHERE James John Smith John James Johnson Michael Marcus Williams Also, a column alias may be used any of the final clauses of the same query, such as an ORDER BY: SELECT FName AS FirstName, LName AS LastName FROM Goolkicker-com - SQL Notes for Professionals,Enployees ORDER BY LastName DESC However, you may not use SELECT FName AS SELECT LName AS FROM FROM Enployees ORDER BY LastName DESC To create an alias from these reserved words (SELECT and FROM) This will cause numerous errors on execution. Section 6.3: Select Individual Columns SELECT PhoneNunber Email PreferredContact FROM Customers ‘This statement will return the columns PhoneNunber, Enail, and Preferredcontact from all rows of the Customers table. Also the columns will be returned in the sequence in which they appear in the SELECT clause. The result will be: PhoneNumber Email PreferredContact 3347927472 william jones@example.com PHONE 2137921892 —_dmiller@examplenet EMAIL NULL richard0123@example.com EMAIL If multiple tables are joined together, you can select columns from specific tables by specifying the table name before the column name: (table_nane] .[colunn_nane SELECT Customers. PhoneNunber. Customers. Enail Customers .PreferredContact Orders.Id AS OrderId FROM Customers LEFT JOIN Orders ON Orders.custonerId ~ Customers. Id AS OrderTd means that the Td field of Orders table will be returned as a column named OrderTd, See selecting with column alias for further information, To avoid using long table names, you can use table aliases. This mitigates the pain of writing long table names for ‘each field that you select in the joins. I you are performing a self join (a join between two instances of the same table), then you must use table aliases to distinguish your tables. We can write a table alias like Custoners ¢ or Customers AS ¢, Here ¢ works as an alias for Custoners and we can select let's say Enail like this: ¢ Email. Goolkicker-com - SQL Notes for Professionals, 6SELECT ¢. PhoneNlunber: Email, ©. Preferredcontact, 0.Td AS OrderTd FROM Customers © LEFT JOIN Orders © ON o.CustomerId = ¢.Id Section 6.4: Selecting specified number of records ‘The SQL 2008 standard defines the FETCH FIRST clause to limit the number of records returned. SELECT Té, ProductName, UnitPrice, Package FROM Product ORDER BY UnitPrice DESC FETCH FIRST 1@ ROHS ONLY This standard is only supported in recent versions of some RDMSs. Vendor-specific non-standard syntax is provided in other systems. Progress OpenEdge 11.x also supports the FETCH FIRST ROWS ONLY syntax. ‘Additionally, OFFSET ROWS before FETCH FIRST RONS ONLY allows skipping rows before fetching rows, SELECT Ié, ProductName, UnitPrice, Package FROM Product ORDER BY UnitPrice DESC OFFSET 5 ROWS FETCH FIRST 18 ROWS ONLY The following query is supported in SQL Server and MS Access: SELECT TOP 10 Id, ProductName, UnitPrice, Package FROM Product ORDER BY UnitPrice DESC To do the same in MySQL or PostgreSQL the LIMTT keyword must be used: SELECT Té, ProductName, UnitPrice, Package FROM Product ORDER BY UnitPrice DESC Limit 18 In Oracle the same can be done with RONNUM: SELECT Ié, ProductName, UnitPrice, Package FROM Product WHERE ROWNUM <= 10 ORDER BY UnitPrice DESC Results: 10 records, Id ProductName UnitPrice Package 38 Céte de Blaye 263.50 12 - 75 cl bottles 29 Thiringer Rostbratwurst 123.79 50 bags x 30 sausgs. 9 Mishi Kobe Niku 97.00 18 - 500 g pkgs Goolkicker-com - SQL Notes for Professionals, e20 Sir Rodney's Marmalade 81.00 30 gift boxes 18 Carnarvon Tigers 62.50 16 kg pkg. 59 Raclette Courdavault 55.00 5 ka pkg 51 Manjimup Dried Apples 53.09 50 - 300g pkgs. 62 Tarte au sucre 49.30 48 pies 43° Ipoh Coffee 46.00 16 - 500 g tins 28 -Rossle Sauerkraut 45,60 25 = 825 9 cans Vendor Nuances Itis important to note that the TOP in Microsoft SQL operates after the WHERE clause end will return the specified number of results if they exist anywhere in the table, while ROHNUM Works as part of the NHERE clause so if other conditions do not exist in the specified number of rows at the beginning of the table, you will get zero results when there could be others to be found. Section 6.5: Selecting with Condition ‘The basic syntax of SELECT with WHERE clause is: SELECT colunn’, column2, column FROM table_nane WHERE [condition] The {condition} can be any SQL expression, specified using comparison or logical operators like >, <, =, <>,>=, LIKE, NOT, IN, BETWEEN etc. The following statement returns all columns from the table ‘Cars’ where the status column is ‘READY’ SELECT * FROM Care WHERE status = ‘READY’ See WHERE and HAVING for more examples. Section 6.6: Selecting with CASE When results need to have some logic applied ‘on the fly’ one can use CASE statement to implement it. SELECT CASE WHEN Col1 < S@ THEN ‘under’ ELSE ‘over’ END threshold FROM TableName also can be chained SELECT CASE WHEN Colt < 5 THEN ‘under WHEN Col1 > 59 AND Coll <10@ THEN ‘between ELSE ‘over" END threshold FROM TableName ‘one also can have CASE inside another CASE staternent SELECT CASE WHEN Coll < 58 THEN ‘under ELSE CASE WHEN Coll > 5@ AND Coll <100 THEN Colt ELSE ‘over” END END threshold Goolkicker-com - SQL Notes for Professionals, 20FROM TableName Section 6.7: Select columns which are named after reserved keywords When a column name matches a reserved keyword, standard SQL requires that you enclose it in double quotation marks: SELECT “ORDER™ 1D FROM ORDERS Note that it makes the column name case-sensitive, Some DBMSes have proprietary ways of quoting names. For example, SQL Server uses square brackets for this Purpose: SELECT Order] 1D FROM ORDERS. while MySQL (and MariaDB) by default use backticks: SELECT Order ad FROM orders Section 6.8: Selecting with table alias SELECT e.Fname, @.LName FROM Employees e The Employees table is given the alias e’ directly after the table name. This helps remove ambiguity in scenarios where multiple tables have the same field name and you need to be specific as to which table you want to return data from, SELECT e.Fname, @.LNane, m.Frane AS ManagerFirstName FROM Employees e JOIN Managers m ON e.MenagerTd = m.Id Note that once you define an alias, you can't use the canonical table name anymore. ie., SELECT e.Fname, Employees.LName, m.Fname AS ManagerFirstName FROM Employees e JOIN Managers m ON e.Managerid = m.Id would throw an error. Itis worth noting table aliases ~ more formally ‘range variables’ -- were introduced into the SQL language to solve the problem of duplicate columns caused by INNER JOIN. The 1992 SQL standard corrected this earlier design flaw by introducing NATURAL. JOTN (implemented in mySQL, PostgreSQL and Oracle but not yet in SQL Server), the result ‘of which never has duplicate column names. The above example is interesting in that the tables are joined on Goolkicker-com - SQL Notes for Professionals, acolumns with different names (Id and Manager1d) but are not supposed to be joined on the columns with the same name (LNane, FName), requiring the renaming of the columns to be performed before the join: SELECT Fame, LName, ManagerFirstName FROM Employees NATURAL, JOIN ( SELECT Td AS ManagerT¢, Frame AS ManagerFirstName FROM Managers ) m Note that although an alias/range variable must be declared for the dervied table (otherwise SQL will throw an. error), it never makes sense to actually use it in the query. Section 6.9: Selecting with more than 1 condition ‘The AND keyword is used to add more conditions to the query Name Age Gender sam 18 M John 21 M Bob 22 M Mary 23 F SELECT name FROM persons WHERE gender = °M’ AND age > 20 This will return: Name John Bob using 08 keyword SELECT name FROM persons WHERE gender - 'M' OR age < 28 This will return name sam John Bob ‘These keywords can be combined to allow for more complex criteria combinations: SELECT name FROM persons WHERE (gender = "M' AND age < 28) 08 (gender = “F' AND age > 28) This will return: sam Mary Goolkicker-com - SQL Notes for Professionals, 2Section 6.10: Selecting without Locking the table Sometimes when tables are used mostly (or only) for reads, indexing does not help anymore and every little bit counts, one might use selects without LOCK to improve performance. SQL Server SELECT * FROM TableNane WITH (nolock) Mysql SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED SELECT * FROM TableNane SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ Oracle TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; SELECT * FROM TableName paz SELECT * FROM TableName WITH UR Where UR stands for "uncommitted read” ble results, Irused on table that has record modifications going on might have unpredict Section 6.11: Selecting with Aggregate functions Average The AVO() aggregate function will return the average of values selected. SELECT AVG(Salary) FROM Employees Aggregate functions can also be combined with the where clause. SELECT AVG(Salary) FROM Employees where DepartmentId = 1 ‘Aggregate functions can also be combined with group by clause. If employee is categorized with multiple department and we want to find avg salary for every department then we can use following query. SELECT AVG(Salary) FROM Employees GROUP BY DepartmentId Minimum. The NTN() aggregate function will return the minimum of values selected. SELECT MIN(Salary) FROM Enployees Maximum The HX() aggregate function will return the maximum of values selected. SELECT WAx(Salary) FROM Employees count ‘The COUNT () aggregate function will return the count of values selected. SELECT Count(*) FROM It can also be combined with where conditions to get the count of rows that satisfy specific conditions. oyees Goolkicker-com - SQL Notes for Professionals, BSELECT Count(*) FROM Employees where ManagerTd TS NOT NULL Specific columns can also be specified to get the number of values in the column. Note that NULL values are not counted. Select Count(Managerid) from Employees Count can also be combined with the distinct keyword for a distinct count. Select Count (DISTINCT DepartmentId) from Employees sum The SUM() aggregate function returns the sum of the values selected for all rows, SELECT SUM(Salary) FROM Employees Section 6.12: Select with condition of multiple values from column SELECT * FROM Cars WHERE status IN ( ‘Waiting’, ‘Working This is semantically equivalent to SELECT * FROM Care WHERE ( status ‘Waiting’ OR status "Working" lle, value IN ( ‘ACCOUNTING’ GROUP BY department Ifyou need to filter the results after the grouping has been done, e.g, to see only departments whose average income is larger than 1000, you need to use the HAVING clause: SELECT departnent, AVG(income) FROM employees WHERE department <> ‘ACCOUNTING’ GROUP BY department HAVING avo(incore) > 1900 Goolkicker-com - SQL Notes for Professionals, aSection 6.14: Selection with sorted Results SELECT FROM Employees ORDER BY LNane ‘This statement will return all the columns from the table Employees, Id FName LName PhoneNumber 2 John — Johnson 2468101214 1 James Smith 1234567890 3 Michael Williams 1357911131 SELECT * FROM Employees ORDER BY LName DESC or SELECT * FROM Employees ORDER BY LName ASC This statement changes the sorting direction. One may also specify multiple sorting columns. For example: SELECT * FROM Employees ORDER BV LName ASC, FName ASC This example will sort the results first by LName and then, for records that have the same LNane, sort by FName, This will give you a result similar to what you would find in a telephone book. In order to save retyping the column name in the ORDER BY clause, itis possible to use instead the column's number, Note that column numbers start from 1. SELECT Té, FName, LName, PhoneNunber FROM Employees ORDER BY 3 You may also embed a CASE statement i € ORDER @Y clause. SELECT Id, FName, LName, PhoneNunber FROM Employees ORDER BY CASE WHEN LName='Jones’ THEN @ ELSE 1 END ASC ‘This will sort your results to have all records with the LName of "jones" at the top. Section 6.15: Selecting with null SELECT Name FROM Customers WHERE PhoneNumber TS NULL ise 1S NULL OF IS NOT NULL instead. Selection with nulls take a different syntax. Don't use Section 6.16: Select distinct (unique values only) SELECT DISTINCT ContinentCode FRO Countries This query will return all DISTINCT (unique, different) values from ContinentCode column from Countries table Continentcode oc eu Goolkicker-com - SQL Notes for Professionals, 2As. ‘SOLFiddle Demo Section 6.1 : Select rows from multiple tables SELECT * FROM tabley table? SELECT ‘tablet .colunnt tablet .colunn2, table?.colunnt FROM tablet table2 This s called cross product in SQL itis same as cross product in sets, ‘These statements return the selected colurnns from multiple tables in one query. ‘There is no specific relationship between the columns returned from each table, Goolkicker-com - SQL Notes for Professionals, 26Chapter 7: GROUP BY Results of a SELECT query can be grouped by one or more columns using the GROUP BY staternent: all results with the same value in the grouped columns are aggregated together. This generates a table of partial results, instead of ‘one result. GROUP BY can be used in conjunction with aggregation functions using the HAVING statement to define how non-grouped columns are aggregated, Section 7.1: Basic GROUP BY example It might be easier if you think of GROUP BY as “for eacl for the sake of explanation, The query below: SELECT EnplD, SUM (MonthlySalary) FROM Employee GROUP BY EmpID is saying: "Give me the surn of MonthlySalary’s for each EmpID" So if your table looked like this: |EnpID|MonthtySatary| Result 11/200] 121300] ‘Sum wouldn't appear to do anything because the sum of one number is that number. On the other hand ifit looked like this: |EnpID|MonthtySatary| [200 I [2 [300 i Result Goolkicker-com - SQL Notes for Professionals, 7]|1]500] [21300] Then it would because there are two EmpID 1's to sum together. Section 7.2: Filter GROUP BY results using a HAVING clause A HAVING clause filters the results of a GROUP BY expression. Note: The following examples are using the Library ‘example database. Examples: Return all authors that wrote more than one book (live example) SELECT ald a. Name. COUNT(*) BooksWratten FROM BooksAuthors ba INNER JOIN Authors @ ON a.id = ba.authorid GROUP BY ald a Name HAVING COUNT(+) > 1 ~~ equals to HAVING BooksWritten > 1 Return all books that have more than three authors (live example. SELECT b.ld b.Title, COUNT(*) NunberofAuthors FROM BooksAuthors ba INNER JOIN Books b ON b.id ~ ba.bookid GROUP BY b.td b.title HAVING COUNT(*) > 3. ~~ equals to HAVING NunberOfauthors > 3 Section 7.3: USE GROUP BY to COUNT the number of rows for each unique entry in a given column Let's say you want to generate counts or subtotals for a given value in a column, Given this table, "Westerosians" Name GreatHousedlllegience Arya Stark Cercei_Lannister Myrcella Lannister Yara Greyjoy Catelyn Stark Goolkicker-com - SQL Notes for Professionals, 28Sansa Stark Without GROUP BY, COUNT will simply return a tot number of rows: SELECT Count(*) Number_of_Mesterosians FROM Westerosians returns, Number_of Westerosians 6 But by adding GROUP BY, we can COUNT the users for each value in a given column, to return the number of people in a given Great House, say: SELECT GreatHouseAllegience House, Count(») Number_of_Westerosians FROM Westerosians GROUP BY GreatHouseAllegience returns... House Number_of Westerosians Stark 3 Greyjoy 1 Lannister 2 It's common to combine GROUP BY with ORDER BY to sort result by largest or smallest category: SELECT GreatHouseAllegience House, Count(*) Number_of_Westerosians FROM Westerosians GROUP BY GreatHouseAllegience ORDER BY Nunber_of Mesterosians Desc returns, House Number_of Westerosians k 3 Lannister 2 Greyjoy 1 Section 7.4: ROLAP aggregation (Data Mining) Description The SQL standard provides two additional aggregate operators. These use the polymorphic value “ALL” to denote the set of all values that an attribute can take. The two operators are: ‘+ with date cube that it provides all possible combinations than the argument attributes of the clause. ‘+ with rol1 up that it provides the aggregates obtained by considering the attributes in order from left to right compared how they are listed in the argument of the clause. ‘SQL standard versions that support these features; 1999,2003,2006,2008,2011. Examples Goolkicker-com - SQL Notes for Professionals, 29Consider this table: Food Brand Total_amount Pasta Brandt 100 Pasta Brand2 250 Pizza Brand2 300 With cube select Food, Brand, Total_anount fron Table group by Food, Brand,Total_amount with cube Food Brand Total_amount Pasta Brand’ 100 Pasta Brand2 250 Pasta ALL 350 Pizza Brand2 300 Pizza ALL 300 ALL Brandt 100 ALL Brand2 550 ALL ALL 650 With roll up select Food, Brand, To from Table group by Food, Brand,Total_amount with roll up Food Brand Total_amount Pasta Brand’ 100 Pasta Brand2 250 Pizza Brand2 300 Pasta ALL 350 Pizza ALL 300 ALL ALL 650 Lanount Goolkicker-com - SQL Notes for Professionals, 30Chapter 8: ORDER BY Section 8.1: Sorting by column number (instead of name) You can use a colurnn’s number (where the leftmost column is 1')to indicate which column to base the sort on, Instead of describing the column by its name. Pro: If you think it's likely you might change column names later, doing so won't break this code. Con: This will generally reduce readability of the query It's instantly clear what ‘ORDER BY Reputation’ means, while ‘ORDER BY 14' requires some counting, probably with a finger on the screen.) ‘This query sorts result by the info in relative column position 3 from select statement instead of column name Reputation. SELECT DisplayName, JoinDate, Reputation FROM Users ORDER BY 3 DisplayName JoinDate Reputation Community 2008-09-15 4 Jerrod Dixon 2008-10-03 11739 Geoff Dalgas 2008-10-03 12567 Joel Spolsky 2008-09-16 25784 Jeff Atwood 2008-09-16 37628 Section 8.2: Use ORDER BY with TOP to return the top x rows based on a column's value In this example, we can use GROUP BY not only determined the sort of the rows returned, but also what rows are returned, since we're using TOP to limit the result set. Let's say we want to return the top 5 highest reputation users from an unnamed popular Q&A site, Without ORDER BY ‘This query returns the Top 5 rows ordered by the default, which in this case is "Id", the first column in the table (even though it’s not a column shown in the results) SELECT TOP § DisplayName, Reputation FROM Users. returns, DisplayName Reputation Community 1 Geoff Dalgas 12567 Jerrod Dixon 11739 Jeff Atwood 37628 Joel Spolsky 25784 With ORDER BY SELECT TOP 5 DisplayName, Reputation FROM Users. Goolkicker-com - SQL Notes for Professionals, aORDER BY Reputation dese returns, DisplayName Reputation JonSkeet 865023, Darin Dimitrov 661741 Balusc 650237 Hans Passant 625870 Mare Gravell 601636 Remarks ‘Some versions of SQL (such as MySQL) use a LIMIT clause at the end of a SELECT, instead of TOP at the beginning, for example: SELECT DisplayNane, Reputation FROM Users: ORDER BY Reputation Umit 5 Section 8.3: Customizeed sorting order To sort this table Employee by department, you would use ORDER BY Department. However, if you want a different sort order that is not alphabetical, you have t this can be done with a CASE expression: Name Department Hasan IT Yusuf HR Hillary HR Joe IT Merry HR Ken Accountant SELECT * FROM Employee ORDER BY CASE Departma WHEN “HR: THEN 1 WHEN "Accountant" THEN 2 ELSE 3 END: Name Department Yusuf HR Hillary HR. Merry HR Ken Accountant Hasan IT Joe IT Section 8.4: Order by Alias Due to logical query processing order, alias can be used in order by. map the Department values into different values that sort correctly Goolkicker-com - SQL Notes for Professionals, 2SELECT DisplayNene, JoinDate as jd, Reputation as rep FROM Users: ORDER BY jd, rep ‘And can use relative order of the columns in the select statement .Consider the same example as above and instead of using alias use the relative order like for display name It is 1 , for jd it is 2 and so on SELECT DisplayNane, JoinDate as jd, Reputation as rep FROM Users: ORDER BY 2, 3 Section 8.5: Sorting by multiple columns SELECT DisplayName, JoinDate, Reputation FROM Users ORDER BY joinDate, Reputation DisplayName JoinDate Reputation Community 2008-09-15 1 Jeff Atwood 2008-09-16 25784 Joel Spolsky 2008-09-16 37628 Jarrod Dixon 2008-10-03 11739 Geoff Dalgas 2008-10-03 12567 Goolkicker-com - SQL Notes for Professionals, BChapter 9: AND & OR Operators Section 9.1: AND OR Example Have a table Name Age city Bob 10 Paris Mat 20 Berlin Mary 24 Prague select Name from table where Age>10 AND City="Prague’ Gives Name Mary select Name from table where Age=10 OR City='Prague Gives Name Bob Mary Goolkicker-com - SQL Notes for Professionals,Chapter 10: CASE ‘The CASE expression is used to implement if-then logic Section 10.1: Use CASE to COUNT the number of rows ina column match a condition Use Case ‘CASE can be used in conjunction with Sut to return a count of only those items matching a pre-defined condition. (This is similar to COUNTIF in Excel.) ‘The trick is to return binary results indicating matches, so th for @ count of the total number of matches. 's returned for matching entries can be summed Given this table TtenSales, let's say you want to learn the total number of items that have been categorized as "Expensive" Id Itemid Price PriceRating 1100 345 EXPENSIVE 2145 2.3 CHEAP 3100 345 EXPENSIVE 4100 345 EXPENSIVE 5145 10 AFFORDABLE Query SELECT COUNT(Ed) AS Ttemscount SUM (CASE WHEN PriceRating = ‘Expensive’ THEN 1 ELSE @ END ) AS ExpensiveTtenscount FROM Ttenales ItemsCount ExpensiveltemsCount 5 3 Alternative: SELECT COUNT(Id) 95 TtemsCount SUM ( CASE PriceRating WHEN 'Expensive’ THEN 1 ELSE @ END. ) AS ExpensivertemsCount FROM ItenSales Goolkicker-com - SQL Notes for Professionals,Section 10.2: Searched CASE in SELECT (Matches a boolean expression) ‘The searched CASE returns results when a boolean expression is TRUE. (This differs from the simple case, which can only check for equivalency with an input.) SELECT Id, Itemtd, Price. CASE WHEN Price < 10 THEN “CHEAP WHEN Price < 24 THEN “AFFORDABLE” ELSE" EXPENST END AS PriceRating FROM TrenSales Id Itemid Price PriceRating 1100 345 EXPENSIVE 2145 2.3. CHEAP 3.100 345 EXPENSIVE 4100 345 EXPENSIVE 5145 10 AFFORDABLE Section 10.3: CASE in a clause ORDER BY We can use 1,2,3.. to determine the type of order: SELECT = FROM DEPT ORDER BY (CASE DEPARTMENT WHEN "MARKETING’ THEN 1 WHEN 'SALES' THEN 2 WHEN "RESEARCH THEN 9 WHEN “TRNOVATION” THEN 4 ELSE 5 END. cry ID REGION cry DEPARTMENT EMPLOYEES NUMBER 12 New England Boston MARKETING 9 15 West San Francisco MARKETING 2 9 Midwest Chicago SALES 8 14 Mid-Atlantic NewYork SALES R 5 West Los Angeles RESEARCH " 10 Mid-Atlantic Philadelphia RESEARCH 13 4 Midwest Chicago INNOVATION " 2 Midwest Detroit HUMAN RESOURCES 9 Section 10.4: Shorthand CASE in SELECT CCASE's shorthand variant evaluates an expression (usually a column) against a series of values. This variant is a bit shorter, and saves repeating the evaluated expression over and over again. The ELSE clause can still be used, though: SELECT Id, ItemId, Price. CASE Price WHEN 5 THEN ‘CHEAP’ WHEN 15 THEN “AFFORDABLE” Goolkicker-com - SQL Notes for Professionals, 36ELSE END as PriceRating FROM ItenSales * EXPENSIVE" A word of caution. It's important to realize that when using the short variant the entire statement is evaluated at ‘each WHEN. Therefore the following statement: ect (CASE ABS (CHECKSUM(NENID())) % 4 WHEN @ THEN WHEN 1. THEN WHEN 2 THEN WHEN 3 THEN a Waster a Wes! END may produce a NULL result. That is because at each WHEN NEWID() to WHEN ABS(CHECKSUM(NEWI9())) % 4 = @ THEN WHEN ABS(CHECKSUM(NEWTD())) % 4 = 1 THEN WHEN ABS(CHECKSUM(NEWI9())) % 4 = 2 THEN WHEN ABS(CHECKSUM(NEWTD())) % 4 = 3 THEN END Therefore it can miss all the WHEN cases and result as NULL. is being called again with a new result. Equivalent os Waster ed ours! Section 10.5: Using CASE in UPDATE sample on price increases: UPDATE ItemPrice Price = Price « CASE Itemid WHEN 7 THEN 1.5 WHEN 2 THEN 1.18 WHEN 3 THEN 1.15 ELSE 1.00 END Section 10.6: CASE use for NULL values ordered last in this way ‘0' representing the known values are ranked first, 1" representing the NULL values are sorted by the last ELECT ID REGION cry DEPARTHEN: EMPLOYEES. NUMBER FROM DEPT ORDER BY CASE WHEN REGION IS NULL ELSE @ END, REGION HEN 1 Goolkicker-com - SQL Notes for Professionals, 7ID REGION cry DEPARTMENT EMPLOYEES _NUMBER 10 Mid-Atlantic Philadelphia RESEARCH 13 14 Mid-Atlantic New York SALES 2 9 Midwest Chicago SALES 8 12 New England Boston MARKETING 9 5 West Los Angeles RESEARCH " 15 NULL. San Francisco MARKETING 2 4 NULL Chicago INNOVATION " 2 NULL Detroit HUMAN RESOURCES 9 Section 10.7: CASE in ORDER BY clause to sort records by lowest value of 2 columns Imagine that you need sort records by lowest value of either one of two columns. Some databases could use a non- ‘aggregated 1N() or LEAST() function for this (... ORDER BY NIN(DateT, Date2)), but in standard SQL, you have to use a CASE expression, ‘The CASE expression in the query below looks at the Date? and Date2 columns, checks which column has the lower value, and sorts the records depending on this value. Sample data Id Date? —_Datez + 2017-01-01 2017-01-31 2 2017-01-31 2017.01.03 3 2017-01-31 2017-01-02 4 2017-01-06 2017-01-31 5 2017-01-31 2017-01-05 6 2017-01-04 2017-01-31 Query SELECT Id, Datet, Date FR0M YourTable ORDER BY CASE WHEN COALESCE(Date1, '1753-01-01') < COALESCE(Date2, '1753-01-01') THEN Date FLSE Date? END Results Id Datet —_Datez 2017-01-01 2017-01-31 2017-01-31 2017-01-02 2017-01-31 2017-01-03 2017-01-04 2017-01-31 2017-01-31 2017-01-05 2017-01-06 2017-01-31 Explanation Ruanus ‘As you see row with Té = 1 Is first, that because Date1 have lowest record from entire table 2817-81-81, row where Id = is second that because Date? equals to 2817-01-@2 that is second lowest value from table and so on, ‘So we have sorted records from 2017-@1-@1 to 2017-@1-@6 ascending and no care on which one column Date1 or Date? are those values. Goolkicker-com - SQL Notes for Professionals, 8Chapter 11: LIKE operator Section 11.1: Match open-ended pattern The * wildcard appended to the beginning or end (or both) of a string will allow 0 or more of any character before the beginning or after the end of the pattern to match. Using’%’ in the middle will allow 0 or more characters between the two parts of the pattern to match, We are going to use this Employees Table: IdFName LName PhoneNumber Managerld Departmentid Salary Hire date 1 John Johnson 24681012141 1 400 23-03-2005 2 Sophie Amudsen 24791002111 1 400 11-01-2010 3 Ronny Smith 2462544026 2 1 600 06-08-2015 4 Jon Sanchez 24541246021 1 400 23-03-2005, 5S Hilde Knag 24680219112 1 800 01-01-2000 Following statement matches for all records having FName containing string ‘on’ from Employees Table, SELECT * FROM Employees WHERE FName LIKE ‘Son’! Id FName LName PhoneNumber Managerld Departmentid Salary Hire date 3 Ronny Smith 2462544026 2 1 600 06-08-2015 4 Jon Sanchez 24541246021 1 400 23-03-2005, Following statement matches all records having PhoneNumber starting with string '246' from Employees. SELECT * FROM Employees WHERE PhoneNumber LIKE '246%" Id FName LName PhoneNumber Managerld Departmentld Salary Hire date 1 John Johnson 24681012141 1 400 23-03-2005 3 Ronny Smith 2462544026 2 1 600 06-08-2015, 5 Hilde Knag 2468021911 2 1 800 01-01-2000 Following statement matches all records having PhoneNumber ending with string 11" from Employees, SELECT * FROM Employees WHERE PhoneNunber LIKE '%11 IdFName LName PhoneNumber Managerld Departmentld Salary Hire date 2 Sophie Amudsen 24791002111 1 400 11-01-2010 5 Hilde Knag 24680219112 1 800 01-01-2000 All records where Fname 3rd character is'n’ from Employees, SELECT * FROM Employees WHERE FName LIKE (two underscores are used before 'n' to skip first 2 characters) Id FName LName PhoneNumber Managerld Departmentid Salary Hire date 3 Ronny Smith 2462544026 2 1 600 06-08-2015 4 Jon Sanchez 24541246021 1 400 23-03-2005, Goolkicker-com - SQL Notes for Professionals, 9Section 11.2: Single character match To broaden the selections of a structured query language (SQL-SELECT) statement, wildcard characters, the percent sign (%) and the underscore (.), can be used, ‘The _ (underscore) character can be used as a wildcard for any single character in a pattern match, Find all employees whose Frame start with j' and end with 'n’ and has exactly 3 characters in Frame. SELECT = FROM Employees WHERE FNane LIKE ‘3.0 _ (underscore) character can also be used more than ance as a wild card to match patterns. For example, this pattern would match *jon’ ‘These names will not be shown "jn","john’,"jordan', "justin", "jason’ ‘one underscore is used and it can skip exactly one character, so result must be of 3 character Fame. ‘julian, \jllian’, Joann” because in our query For example, this pattern would match "Last", "Lost", "Halt, etc SELECT * FROM Employees WHERE FName LIKE ' ALT! Section 11.3: ESCAPE statement in the LIKE-query if you implement a text-search as LTKE-query, you usually doit ke this: se.ect * FROM Thatever WHERE SomeField LIKE CONCAT("%', @in_SearchText, '%: However, (apart from the fact that you shouldn't necessarely use LIKE when you can use fultext-search) this creates a problem when somebody inputs text ike "503" or "a_b So {instead of switching to fulltextsearch), you can solve that problem using the \7kE-escape statement: SELECT * FROM T_Whatever WHERE SomeField LIKE CONCAT("%', @in_SearchText ESCAPE '\" That means \ will now be treated as ESCAPE character. This means, you can now just prepend \ to every character in the string you search, and the results will start to be correct, even when the user enters a special character like % eg. string stringToSearch = "abe_def 50% string newString = foreach(char © in stringToSearch) newString += @\" + © sqlCnd Parameters Add("ein_SearchText", newString tead of sqlCnd.Paraneters.Add("Pin_SearchText", stringToSearch Note: The above algorithm is for demonstration purposes only. It will not work in cases where 1 grapheme consists out of several characters (utF-8). e.g. string stringToSearch = “Les Mise\u@301rables" ; Youlllneed to do this, Goolkicker-com - SQL Notes for Professionals, 40for each graoheme, not for each character. You should not use the above algorithm if you're dealing with Asian/East-Asian/South-Asian languages, Or rather, if you want correct cade to begin with, you should just do that for each graphemeCluster See also ReverseString, a Ct interview-question Section 11.4: Search for a range of characters Following staternent matches all records having FName that starts with a letter from A to F from Employees Table. SELECT « FROM Employees WHERE FName LIKE '[A-F]% Section 11.5: Match by range or set Match any single character within the specified range (e.g: [2-f]) or set (e.g.: [abedef]) ‘This range pattern would match "gary" but not "mary" SELECT FROM Employees WHERE FName LIKE '[a-g] This set pattern would match "mary" but not "gary": SELECT * FROM Employees WHERE Frame LIKE ‘ [Imnop]ary’ The range or set can also be negated by appending the * caret before the range or set ‘This range pattern would not match “gary" but will match "mary" SELECT * FROM Employees WHERE FName LIKE '[*a-glary’ ‘This set pattern would nat match "mary" but will matchgary" SELECT * FROM Employees WHERE Fname LIKE '[*Imnop]ary’ Section 11.6: Wildcard characters wildcard characters are used with the SQL LIKE operator. SQL wildcards are used to search for data within a table, Wildcards in SQL are:%, _, (charlist], [*charlist) % - A substitute for zero or more characters, Eg: //selects all custoners with a City starting with SELECT + FROM Custoners WHERE City LIKE ‘Low ‘selects all custoners with a City containing the pattern "es" SELECT + FROM Custoners WHERE City LIKE ‘est _-Asubstitute for @ single character Eg://selects all customers with @ City starting with any character, followed by "erlin’ SELECT * FROM Customers Goolkicker-com - SQL Notes for Professionals,WHERE City LIKE ‘_erlin’ [charlist] - Sets and ranges of characters to match Eg://selects all customers with a City starting with SELECT = FROM Customers WHERE City LIKE ‘[ad1]8" /eelects all customers with a City starting with SELECT * FROM Customers WHERE City LIKE '[a-c]¥’ [charlist] - Matches only a character NOT specified within the brackets £g://selects a11 customers with a City starting with a character that is not SELECT * FROM Customers WHERE City LIKE '[*ap1]%" SELECT = FROM Customers WHERE City NOT LIKE ‘[apl]%' and city like * Goolkicker-com - SQL Notes for Professionals, a2Chapter 12: IN clause Section 12.1: Simple IN clause To get records having any ofthe given ads select * from products where id in (1,8,3) The query above is equal to select « from products where id = 1 Section 12.2: Using IN clause with a subquery SELECT * FROM customers WHERE td IN ( SELECT DISTINCT custoner_id FROM orders The above will give you all the customers that have orders in the system. Goolkicker-com - SQL Notes for Professionals,Chapter 13: Filter results using WHERE and HAVING Section 13.1: Use BETWEEN to Filter Results ‘The following examples use the Item Sales and Customers sample databases. Note: The BETWEEN operator is inclusive. Using the BETWEEN operator with Numbers: SELECT = From ItenSales WHERE Quantity BETWEEN 1@ AND 17 This query will return all TtenSales records that have a quantity that is greater or equal to 10 and less than or equal to 17. The results will look lke: Id SaleDate Itemid Quantity Price 1 2013-07-01 100 10 345 4 2013-07-23 10015 345 5 2013.07-24145 10 345 Using the BETWEEN operator with Date Values: SELECT From Itensales WHERE SaleDate BETWEEN '2613-07-11' AND ‘2013-05-24 ‘This query will return all ItenSales records with a Salebate that is greater than or equal to July 11, 2013 and less than or equal to May 24, 2013. Id SaleDate Itemid Quantity Price 3 2013-07-11 100 20 345 4 2013-07-23 10015 345 5 2013-07-24145 10 345 When comparing datetime values instead of dates, you may need to convert the datetime values into a date values, or add or subtract 24 hours to get the correct results. Using the BETWEEN operator with Text Values: SELECT I¢, FName, LName FROM Customers WHERE LName BETWEEN "D’ AND ‘L* Live example: SOL fiddle This query will return all customers whose name alphabetically falls between the letters 'D’ and’'L’.In this case, Customer #1 and #3 will be returned. Customer #2, whose name begins with aM’ will not be included, Id FName LName Goolkicker-com - SQL Notes for Professionals, 41 William Jones 3 Richard Davis, Section 13.2: Use HAVING with Aggregate Functions Unlike the WHERE clause, HAVING can be used with aggregate functions, ‘An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement (Wikipedia). Common aggregate functions include COUNT(), SUN(), HIN(), and MAX(). ‘This example uses the Car Table from the Example Databases. SELECT CustomerTd, COUNT(Id) AS [Number of Cars) FROM Cars GROUP BY CustomerId HAVING COUNT(Id) > 1 This query will return the CustomerTd and Number of Cars count of any customer who has more than one car. In this case, the only customer who has more than one car is Customer #1 The results will look like: Customerld Number of Cars 1 2 Section 13.3: WHERE clause with NULL/NOT NULL values SELECT = FROM Employees WHERE ManagerTd IS NULL ‘This statement will return all Employee records where the value of the ManagerId column is NULL. The result will be: Id Fame Lame _—-PhoneNunber Manager Department Id 1 James Smith 1234567890 NULL 1 SELECT * FROM Employees WHERE ManagerId IS NOT NULL This statement will return all Employee records where the value of the ManagerTd is not NULL. The result will be: Id FNane LWane —PhoneNumber —ManagerId__ Department Id 2 John Johnson 24681012141 1 3 Michael Williams 13579111311 2 4 Johnathon smith = 12121212122 1 Goolkicker-com - SQL Notes for Professionals, 5Note: The same query will not return results if you change the WHERE clause to WHERE ManagerId ~ NULL oF WHERE ManagerTd <> NULL. Section 13.4: Equality SELECT * FROM Employees ‘This statement will return all the rows from the table Employees. Id Flame Lame PhoneNunber ManagerIdDepartmentId Salary Hire_date Createdbate Vodifiedbate 1 Janes Smith 1234567890 NULL 1 1900 01-01-2002 01-01-2002 01-01-2002 2 John Johnson 2468101214 1 1 409 23-03-2005 23-03-2005 1-01-2002 3 Michael Williams 13579111311 2 609 12-05-2009 12-05-2009 NULL 4 Johnathon Smith 12121212122 1 500 24-07-2016 24-07-2016, 1-01-2002 Using a WHERE at the end of your SELECT staternent allows you to limit the returned rows to a condition. In this case, where there is an exact match using the = sign: SELECT * FROM Employees WHERE DepartmentId = 1 will only return the rows where the DepartmentId is equal to 1 Id Flame Lame PhoneNNunberManagerId DepartmentId Salary Hire date CreatedDate Vodifiedbate 1 James Smith 1234567890 NULL. 1 1000 01-01-2002 01-01-2002 01-01-2002 2 John Johnson 2468101214 1 1 400 23-03-2005 23-03-2005 1-01-2002 4 — Johnathon Smith 12121212122 1 500 24-07-2016 24-07-2016, 01-01-2002 Section 13.5: The WHERE clause only returns rows that match its criteria Steam has a games under $10 section of their store page. Somewhere deep in the heart of their systems, there's probably a query that looks something like: SELECT * FROM Ttens WHERE Price < 10 Section 13.6: AND and OR You can also combine several operators together to create more complex WHERE conditions, The following examples Use the Employees table Td Flame Lame PhoneNunber ManagerIdDepartmentId Salary Hire_date Goolkicker-com - SQL Notes for Professionals, 46Createdbate Voditiedbate 1 James Smith 1234567899 NULL. 1 102 01-01-2002 01-01-2002 1-01-2002 2 John Johnson 2468101214 1 1 409 23-03-2005 23-03-2005 1-01-2002 3 Michael Williams 13579111311 2 609 12-05-2009 12-05-2009 NULL 4 Johnathon Smith 12121212122 1 508 24-07-2016 24-67-2816 1-01-2002 AND SELECT * FROM Employees WHERE DepartmentId = 1 AND NanagerId = 1 will return: Id Flame Lame PhoneNiunber ManagerId DepartmentId Salary Hire_date CreatedDate VodifiedDate 2 John Johnson 2468101214 1 1 400 23-03-2005 23-03-2005 1-01-2002 OR SELECT * FROM Employees WHERE DepartmentId = 2 OR ManagerId = 2 will return Id Flame Lame PhoneNunber ManagerId DepartmentId Salary Hire_date Createdbate Vodifiedbate 3 Michael Williams 13579111311 2 609 12-05-2009 12-65-2009 NULL 4 Johnathon Smith 12121212122 1 508 24-07-2016 24-67-2816, 61-61-2862 Section 13.7: Use IN to return rows with a value contained in a list ‘This example uses the Car Table from the Example Databases. sevect FROM Cars WHERE TotalCost IN (120, 200, 300) This query will return Car #2 which costs 200 and Car #3 which costs 100. Note that this is equivalent to using multiple clauses with oR, e.g, SELECT FROM Cars WHERE TotalCost = 190 OR TotalCost = 200 OR Totalcost = 300 Section 13.8: Use LIKE to find matching strings and substrings See full documentation on LIKE operator. Goolkicker-com - SQL Notes for Professionals, a7‘This example uses the Employees Table from the Example Databases, SELECT + FROM Employees WHERE FName LIKE ‘John This query will only return Employee #1 whose first name matches john’ exactly. SELECT = FROM Employees WHERE FName Like ‘John’ ‘Adding * allows you to search for a substring: + John’s will return any Employee whose name begins with ‘John’, followed by any amount of characters ‘+ ‘John - will return any Employee whose name ends with John’, proceeded by any amount of characters ‘+ John’ - will return any Employee whose name contains ‘John’ anywhere within the value In this case, the query will return Employee #2 whose name is John’ as well as Employee #4 whose name is ‘Johnathon’ Section 13.9: Where EXISTS Willselect records in TableNane that have records matching in TableNane? SELECT * FROM TableName t WHERE EXISTS ( SELECT 1 FROM TableNane1 1 where t.Id = t1.Id) Section 13.10: Use HAVING to check for multiple conditions in a group Orders Table Customerid Productid Quantity Price 1 2 5 100 1 3 2 200 1 4 1 500 2 1 4 50 3 5 6 700 To check for customers who have ordered both - ProductID 2 and 3, HAVING can be used select custonerTd from orders where productID in (2,3) group by custonertd having count(distinct productID) = 2 Return value: customerld 1 The query selects only records with the productIDs in questions and with the HAVING clause checks for groups Goolkicker-com - SQL Notes for Professionals, 48having 2 productids and not just one, Another possibility would be select custonerTd from orders group by custonertd having sum(case when productID = 2 then 1 else @ end) > @ ‘and sum(case when praductIO = 3 then 1 else @ end) > @ ‘This query selects only groups having at least one record with productID 2 and at least one with productlD 3, Goolkicker-com - SQL Notes for Professionals, 2Chapter 14: SKIP TAKE (Pagination) Section 14.1: Limiting amount of results ISO/ANSI SQL: SELECT * FROM TableName FETCH FIRST 28 RONS ONLY: MySQL: PostgreSQL: SQlite: SELECT * FROH TableNane LIMIT 20 Oracle: seLecr 14 colt Ron (SELECT Ta con ron_nunber() over (order by 1d) RowNunber FROM TableNane’ WHERE RowNunber <= 20 SQL Server: SELECT TOP 20 FROM dbo. (Sale! Section 14.2: Skipping then taking some results (Pagination) ISO/ANSI SQL: SELECT I¢, Colt FROM TableName ORDER BY Td OFFSET 28 RONS FETCH NEXT 20 ROMS ONLY. MysQu: SELECT * FROM TableName LIMIT 20, 20; -- offset, limit Oracle; SQL Server: SELECT I6. colt FROM (SELECT Td colt row_nunber() over (order by Td) RowNumber FROM TableNare) WHERE RowNunber BETWEEN 21 AND 40 PostgreSQL; SQLite: SELECT * FROM TableName LIMIT 20 OFFSET 20: Goolkicker-com - SQL Notes for Professionals, 50Section 14.3: Skipping some rows from result ISO/ANSI SQL: SELECT 16, con FROH TableNane ORDER BY Td OFFSET 28 RONS MySQL: SELECT * FROM TableName LIMIT 20, 42424242424242 =- skips 29 for take use very large number that is more than rows in table Oracle: SELECT 16, colt FROM (SELECT Id colt rownunber() over (order by Id) RowNumber FROM TableNane’ WHERE RowNunber > 20 PostgreSQL: SELECT * FROM TableName OFFSET 26 sqlite: SELECT * FROM TableName LIMIT -1 OFFSET 20: Goolkicker-com - SQL Notes for Professionals,Chapter 15: EXCEPT Section 15.1: Select dataset except where values are in this other dataset --dataset schenas must be SELECT SELECT SELECT SELECT SELECT EXCEPT SELECT "patat’ Date2 “patas’ Dates “Datas* "Datas* —-Returns Datat, "column ‘Column “column ‘Column “column “column Date2, Dated, and Datas identical UNION ALL UNION ALL UNION ALL UNTON AL Goolkicker-com - SQL Notes for Professionals,Chapter 16: EXPLAIN and DESCRIBE Section 16.1: EXPLAIN Select query ‘An Explain infront of a select query shows you how the query will be executed. This way you to see if the query uses an index or if you could optimize your query by adding an index. Example query: explain select * from user join data on user.test = data. tk_user Example result: id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE user index test, test 5 (mutt) 1 Using where; Using index 1 SIMPLE data ref fuser fkuser 5 user.test 1 (null) on type you see if an index was used. In the column possible_keys you see if the execution plan can choose from different indexes of if none exists. key tells you the acutal used index. key_len shows you the size in bytes for one index item. The lower this value is the more index items fit into the same memory size an they can be faster processed. rons shows you the expected number of rows the query needs to scan, the lower the better. Section 16.2: DESCRIBE tablename; DESCRIBE and EXPLATN are synonyms, DESCRIBE on a tablename returns the definition of the columns. DESCRIBE tablenane Exmple Result: COLUMN_NAME COLUNN_TYPETS_NULLABLE COLUMN_KEY COLUMN_DEFAULT EXTRA ad ant(11 No PRE e aute_inerenent. test verchar(255) YES. (nw) Here you see the column names, followed by the columns type. It shaws if nul is allowed in the column and if the column uses an Index. the default value is also displayed and if the table contains any special behavior like an auto_increment. Goolkicker-com - SQL Notes for Professionals, 35Chapter 17: EXISTS CLAUSE Section 17.1: EXISTS CLAUSE Customer Table Id FirstName LastName 1 Ozgur—Orturk 2 Youssel Medi 3 Henry — Tal Order Table Id Customerid Amount 12 123.50 23 14.80 Get all customers with a least one order SELECT * FROM Customer WHERE EXISTS SELECT * FROM Order WHERE Order CustomerTd-Customer Result Id FirstName LastName 2 Youssef Medi B Henry — Tai Get all customers with no order SELECT * FROM Customer WHERE NOT EXISTS ( SELECT * FROM Order WHERE Order.CustomerId = Custoner. Id Result Id FirstName LastName 1 Ozgur Ozturk Purpose EXISTS, IN and JOTN could sometime be used for the same result, however, they are not equals ‘+ EXISTS should be used to check if a value exist in another table ‘= INshould be used for ‘+ JOIN should be used to retrieve data from other(s)table(s) ic list Goolkicker-com - SQL Notes for Professionals, 34Chapter 18: JOIN JOIN is a method of combining (joining) information from two tables. The result is a stitched set of columns from both tables, defined by the join type (INNER/OUTER/CROSS and LEFT/RIGHT/FULL, explained below) and join criteria (how rows from both tables relate). A table may be joined to itself or to any other table. If information from more than two tables needs to be accessed, multiple joins can be specified in a FROM clause. Section 18.1: Self Join A table may be joined to itself, with different rows matching each ather by some condition. In this use case, aliases must be used in order to distinguish the two occurrences of the table In the below example, for each Employee in the example database Employees table, a record is returned containing the employee's first name together with the corresponding first name of the employee's manager. Since managers are also employees, the table is joined with itself SELECT e.FName AS “Employee” mn.FNeme AS "Manager’ FROM Employees € JOIN Employees m ON e.ManagerId = m.Id ‘This query will return the following data: Employee Manager John James Michael james Johnathon John So how does this work? ‘The original table contains these records: IdFName —LName PhoneNumber Managerld Departmentid Salary HireDate 1 James Smith 1234567890 NULL 1 1000 01-01-2002 2 John Johnson 24681012141 1 400 23-03-2005, 3 Michael Williams 13579111311 2 600 12-05-2009 4 Johnathon Smith 12121212122 1 500 24-07-2016 ‘The frst action is to create a Cartesian product of all records in the tables used in the FROM clause, In this case It's the Employees table twice, so the intermediate table will look like this (I've removed any fields not used in this example): ede.FName_e.Managerld m.ld m.FName m.Managerld 1 James NULL 1 James NULL 1 James NULL 2 John 1 1 James NULL 3 Michael 1 Goolkicker-com - SQL Notes for Professionals, 351 James NULL 4 Johnathon 2 2 John 4 1 James NULL 2 John 1 2 John 4 2 John 4 3 Michael 1 2 John 4 4 Johnathon 2 3) Michael 1 1 James NULL 3 Michael 1 2 John 4 3) Michael 1 3) Michael 1 3° Michael 1 4 Johnathon 2 4 Johnathon 2 1 James NULL 4° Johnathon 2 2 John 4 4° Johnathon 2 3) Michael 1 4° Johnathon 2 4 Johnathon 2 ‘The next action is to only keep the records that meet the JOIN criteria, so any records where the aliased e table ManagerId equals the aliased m table I elde.FName e.Managerld m.ld m.FName m.Managerld 2 John 4 1 James NULL 3 Michael 1 1 James NULL 4 Johnathon 2 2 John 1 ‘Then, each expression used within the SELECT clause is evaluated to return this table: e.FName m.FName John James Michael james Johnathon John Finally, column names e. Fame and m.FNane are replaced by their alias column names, assigned with the AS operator: Employee Manager John James Michael james Johnathon John Section 18.. : Differences between inner/outer joins ‘SQL has various join types to specify whether (non-)matching rows are included in the result: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOTN, and FULL OUTER JOTN (the INNER and OUTER keywords are optional). The figure below underlines the differences between these types of joins: the blue area represents the results returned by the join, and the white area represents the results that the join will not return, Goolkicker-com - SQL Notes for Professionals, 36SELECT eis» FROM Table A ONAKEY = Bikey SQL ON A&oy = Boy (ON Atey = Bry WHERE Bey ERULL WHERE ay 8 NULL SELECT seLect- SELECT Fron Table & FROM Table & FULL OUTER ION Tbie8 8 FULL OUTER JOIN TaD QNAKey = Bkey ONAkey = Bhey ns nore icanse under cese Commons Htrbuton 3.0 Unored cose [WHERE Rey IS NULL thos hakcmons wine omc beck (OR Bkey NULL ross Join SQL Pictorial Presentation (reference) SELECT * FROM tablet CROSS JOIN table? In CROSS JOIN, each row from 1st table joins with all he rows of another table. Ist table contain x rows and y rows in 2nd one the result set will be x * y cows. Below are examples from this answer. For instance there are two tables as below Goolkicker-com - SQL Notes for Professionals, 7Note that (1,2) are unique to A, (3,4) are common, and (5,6) are unique to B. Inner Join ‘An inner join using either of the equivalent queries gives the intersection of the two tables, Le. the two rows they have in common: select * from @ INNER JOIN b on a.a = b.b select a.*,b.* from a,b where a.a = b.b 3/3 4ai4 Left outer join Aleft outer join will give all rows in A, plus any common rows in B: select * from @ LEFT OUTER JOIN b on a.a = b.b: Right outer join Similarly, a right outer join will give all rows in 8, plus any common rows in A: select * from @ RIGHT OUTER JOIN b on a.a = b.b 3 4a mutt | null | 3 4 5 6 Full outer join ‘A full outer join will give you the union of A and B, ie, all the rows in A and all the rows in B. If something in A doesn't have a corresponding datum in 8, then the B portion is null, and vice versa, select * from a FULL OUTER JOIN b on a.a = b.b Goolkicker-com - SQL Notes for Professionals, 38Section 18.3: JOIN Terminology: Inner, Outer, Semi, Anti.. Let's say we have two tables (A and 8) and some of their rows match (relative to the given JOIN condition, whatever it may be in the particular case): Goolkicker-com - SQL Notes for Professionals, 39Table A Table B ANTI - OUTER INNER SEMI JI SEMI LEFT IL RIGHT FULL YANNI NY YaLNO We can use various join types to include or exclude matching or non-matching rows from either side, and correctly name the join by picking the corresponding terms from the diagram above, The examples below use the following test data: CREATE TABLE A ( X varchar(255) PRINARY KEY Goolkicker-com - SQL Notes for Professionals, 60CREATE TABLE 8 ( Y varchar(255) PRINARY KEY INSERT INTO A VALUES (Amy") ( John’ ) Lisa’) ( marco") “Phill INSERT INTO B VALUES (Lisa') “Marco ) (Pha) (Tim) (Vincent ) Inner Join ‘Combines left and right rows that match. Table A Table B 1 { [—— INNER yan SELECT = FROM A JOIN B ON X = Y; Lisa Lisa Narco Marco Phil Phil Left Outer Join ‘Sometimes abbreviated to "left join’. Combines left and right rows that match, and includes non-matching left rows. Goolkicker-com - SQL Notes for Professionals,Table A Table B 1 1 Nuus ourer: Ler SELECT * FROM A LEFT JOIN B ON X = Y. x oY Amy NULL. John NULL. Lisa Lisa Narco Marco Phil Phil Right Outer Join Sometimes abbreviated to "right join’. Combines left and right rows that match, and includes non-matching right rows. Goolkicker-com - SQL Notes for Professionals, @Table A Table B 1 J wine (_piget ——! SELECT * FROM A RIGHT JOIN B ON X = ¥ Lise Lisa Marco Marco Phil Phil NULL Tim NULL Vincent Full Outer Join ‘Sometimes abbreviated to "full join’. Union of left and right outer join. Goolkicker-com - SQL Notes for Professionals, oTable A Table B nus 5 3 Nuts Fu SELECT + FROM A FULL JOIN BON X = Y x oy Any NULL John NULL. Lise Lisa Marco Marco Phil Phil NULL Tim NULL Vineent. Left Semijoin Includes left rows that match right rows vaind Goolkicker-com - SQL Notes for Professionals, aTable A Table B 1 1 sera! ert SELECT * FROM A WHERE X IN (SELECT Y FRON B) Lise Marco Phil Right Semijoin Includes right rows that match left rows. Goolkicker-com - SQL Notes for Professionals, STable A Table B J J seni RIGHT EY IN (SELECT X FROM A) Lisa Narco Phil ‘As you can see, there is no dedicated IN syntax for left vs. right semi join - we achieve the effect simply by switching the table positions within SQL text, Left Anti Semi Join Includes left rows that do net match right rows. Goolkicker-com - SQL Notes for Professionals, 66Table A Table B J 1 ANTI semi! Leet SELECT * FROM A WHERE X NOT IN x Any John (SELECT ¥ FROM 8) WARNING: Be careful if you happen to be using NOT IN on a NULL-able column! More details here, Right Anti Semijoin Includes right rows that do not match left rows. Goolkicker-com - SQL Notes for Professionals,Table A Table B uy \— eiger SELECT * FROM B WHERE Y NOT IN (SELECT X FROM A) Tam Vaneent, ‘As you can see, there is no dedicated NOT IN syntax for left vs. right anti semi join - we achieve the effect simply by switching the table positions within SQL text. Cross Join ‘A Cartesian product of al left with allright rows. SELECT * FROM A CROSS JOIN 8: x oY Lisa John Lisa Lisa Lisa Marco Lisa Phil Lisa Any Marco John Marco Lisa Marco Marco Marco Phil Marco Any Phil John Phil Lisa Phil Narco Phil Phil Phil. Any Thm Goolkicker-com - SQL Notes for Professionals, 6John Tim Lise Tim Marco Tim Phil Tim Any Vincent John Vincent Lisa Vincent Marco Vincent Phil Vincent Cross join is equivalent to an inner join with join condition which always matches, so the following query would have returned the same result: SELECT * FROM A JOIN B ON 1 = 1 SelfJoin This simply denotes a table joining with itself. A sel-join can be any of the join types discussed above. For example, this is a an inner self-join: SELECT * FROM A A1 JOIN A A2 ON LEN(AT.X) < LEN(A2.X: John Lise Marco Narco Marco Narco Phil Section 18.4: Left Outer Join A_Left Outer Join (also known as a Left Join or Outer Join) is a Join that ensures all rows from the left table are represented; if no matching row from the right table exists, its corresponding fields are NULL. The following example will select all departments and the first name of employees that work in that department. Departments with no employees are still returned in the results, but will have NULL for the employee name: SELECT Departments.Nane, Employees .FNane FROM Departments LEFT OUTER JOIN Employees on Departments. Id = Employees Departmentid ‘This would return the following from the example database: Departments.Name Employees.FName HR James HR John HR Johnathon sales Michael Tech NULL So how does this work? Goolkicker-com - SQL Notes for Professionals, @‘There are two tables in the FROM clause: IdFName Lame PhoneNumber Managerid Departmentid Salary HireDate 1 James Smith 1234567890 NULL 1 1000 01-01-2002 2 John Johnson 24681012141 1 400 23-03-2005 3 Michael williams 13579111311 2 600 12-05-2009 4 Johnathon Smith 1212121212 2 1 500 24-07-2016 and Id Name 1 HR 2 Sales 3 Tech First a Cartesian product is created from the two tables giving an intermediate table. ‘The records that meet the join criteria (Departments.ld = Employees. Departmentid) are highlighted in bold; these are passed to the next stage of the query. AAs this is a LEFT OUTER JOIN all records are returned from the LEFT side of the join (Departments), while any records on the RIGHT side are given a NULL marker if they do not match the join criteria, In the table below this will return Tech with NULL IdNameldFName LName PhoneNumber Managerld Departmentld Salary HireDate 1HR 1 James Smith 1234567890 NULL 1 1000 01-01-2002 1 HR 2 John ‘Johnson 24681012141 1 400 23-03-2005, 1 HR 3 Michael Williams 13579111311 2 600 12-05-2009 THR 4 Johnathon Smith 12121212122 1 500 24-07-2016 2 Sales 1 James Smith 1234567890 NULL 1 1000 01-01-2002 2 Sales 2 John Johnson 24681012141 1 400 23-03-2005, 2 Sales 3 Michael Williams 13579111311 2 600 12-05-2009 2 Sales 4 Johnathon Smith 1212121212. 2 1 500 24-07-2016 3 Tech 1 James Smith 1234567890 NULL 1 1000 01-01-2002 3 Tech 2 John Johnson 24681012141 1 400 23-03-2005 3 Tech 3 Michael williams 13579111311 2 600 12-05-2009 3 Tech 4 Johnathon Smith 1212121212 2 1 500 24.07-2016, Finally each expression used within the SELECT clause is evaluated to return our final table: Departments.Name Employees.FName HR James HR John sales Richard Tech NULL Section 18.5: Implicit Join Joins can also be performed by having several tables in the from clause, separated with commas , and defining the relationship between them in the where clause. This technique is called an Implicit join (since it doesn't actually contain a join clause). Goolkicker-com - SQL Notes for Professionals, 70All RDBMSs support it, ut the syntax is usually advised against. The reasons why itis a bad idea to use this syntax are: + Itis possible to get accidental cross joins which then return incorrect results, especially if you have a lot of Joins in the query. + Ifyou intended a cross join, then it is not clear from the syntax (write out CROSS JOIN instead), and someone is likely to change it during maintenance. The following example will select employee's first names and the name of the departments they work for: SELECT e.FName, d.Nane FROM Employee e, Departments d WHERE e.DeptartnentId = ¢.I¢ ‘This would return the following from the example database: e.FNamed.Name James HR John HR Richard Sales Section 18.6: CROSS JOIN Cross join does a Cartesian product of the two members, A Cartesian product means each row of one table is ‘combined with each row of the second table in the join. For example, if TABLEA has 20 rows and TABLES has 20, rows, the result would be 2828 = 49@ output rows. Using example database SELECT d.Name, ¢.FNane FROM Departments 4 (CROSS JOIN Employees Which returns: d.Name e.FName HR James HR John HR Michael HR Johnathon Sales James Sales John Sales Michael Sales Johnathon Tech James Tech John Tech Michael Tech Johnathon Itis recommended to write an explicit CROSS JOIN if you want to do a cartesian join, to highlight that this Is what you want, Goolkicker-com - SQL Notes for Professionals, 7Section 18.7; CROSS APPLY & LATERAL JOIN A very interesting type of JOIN is the LATERAL JOIN (new in PostgreSQL 9.3+), which is also known as CROSS APPLY/OUTER APPLY in SQL-Server & Oracle. The basic idea is that a table-valued function (or inline subquery) gets applied for every row you join. ‘This makes it possible to, for example, only join the first matching entry in another table. ‘The difference between a normal and a lateral join lies in the fact that you can use a column that you previously joined in the subquery that you "CROSS APPLY". syntax: PostgreSQL 9.3+ left | right | inner JOIN LATERAL SQL-Server: CROSS | OUTER APPLY INNER JOIN LATERAL is the same as CROSS APPLY and LEFT JOIN LATERAL is the same as OUTER APPLY Example usage (PostgreSQL 9.34) SELECT * FROM T_Contacts LEFT JOIN T_MAP_ContactsRef_OrganisationalUnit ON MAP_CTCOU_CT_UID = T. MAP_CTOOU_SoftDeletestatus = 1 “-WHERE T_MAP Contacts Ref OrganisationalUnit .MAP_CTCOU_UID I$ NULL -- 989 ntacts.CT_UID AND LEFT JOIN LATERAL SELECT =-MAP_CTCOU_UID Wap_cTCOU_cT_UTD MaP_CTCOU_COU_UID NAP_CTCOU_DateFron MAP_CTCOU_DateTo FROM T_NAP_Contacts_Ref_OrganisationalUnit WHERE MAP_CTCOU_SoftDeleteStatus = 1 AND MAP_CTCOU.CT_UID = T_Contacts.cT_UTD In AND. (_in_bateFrom <= T_NAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateTo) aN (_in_DateTo >= T_MAP_Contacts_Ref OrganisationalUnit .MAP_KTKOE_DateFrom) ) * ORDER BY MAP_CTCOU_ mit 1 teFron Goolkicker-com - SQL Notes for Professionals, 7) AS Firstoe And for SQL-Server SELECT = FROM T-Contacts --LEFT JOIN T.NAP_Contacts_Ref_OrganisationalUnit ON MAP_CTCOU_CT_UID = TContacts.CT_UID AND NAP_CTCOU_SoftDeleteStatus = 1 WHERE T_MAP.Contacts_Ref OrganisationalUnit .MAP_CTCOU_UID IS NULL -- 989 == CROSS APPLY == = INNER JOIN OUTER APPLY = LEFT JOIN ( SELECT TOP 1 =-MAP_CTCOU_UID MaP_CTCOU_CT_UID . MAP_CTCOU_COU_UTD MAP_CTCOU_DateFrom | MAP_CTCOU_DateTo FROM T_MAP_Contacts_Ref _OrganisationalUnit WHERE MAP_CTCOU_SoftDeleteStatus ~ 1 AND MAP_CTCOU.CT_UID = T_Contacts.cT_UID Ie ND (in DateFrom <= T_MAP_Contacts_Ref_OrganisationalUnit .MAP_KTKOE_DateTo) AND (®in_DateTo >= T_NAP_Contacts_Ref_OrganisationalUnit.MAP_KTKOE_DateFrom) ) + ORDER BY MAP_CTCOU_DateFron ) AS Firstoe Section 18.8: FULL JOIN One type of JOIN that is less known, is the FULL JOIN. (Note: FULLJOIN is not supported by MySQL as per 2016) AFULL OUTER JOIN returns all rows from the left table, and all rows from the right table. If there are rows in the left table that do not have matches in the right table, or ifthere are rows in right table that do not have matches in the left table, then those rows willbe listed, too. Example 1 SELECT = FROM Tablet FULL JOIN Table? oN 1 = 2 Example 2: SELECT ‘COALESCE (T_@udget.Year, tYear.Year) AS RPT_BudgetInvear .COALESCE(T_Budget.Value, @.8) AS RPT_Value FROM T Budget FULL JOIN tfu_RPT_AlL_CreateYearInterval (ebudget_year_from, Sbudget_year_to) AS tYear Goolkicker-com - SQL Notes for Professionals, aON tYear.Vear = T.Budget.Year Note that if you're using soft-deletes, you'll have to check the soft-delete status again in the WHERE-clause (because FULL JOIN behaves kind-of like a UNION); It's easy to overlook this litte fact, since you put AP_SoftDeleteStatus = 1 in the join clause. Also, if you are doing a FULL JOIN, you'll usually have to allow NULL in the WHERE-clause; forgetting to allow NULL on a value will have the same effects as an INNER join, which is something you don't want if you're doing a FULL JOIN. Example: SELECT T_AccountPlan.AP_UID T-AecountPLan. AP_Code ‘T_AccountPlan..AP_Lang_EN T_BudgetPositions .BUP_Sudget T_BudgetPositions .BUP_UID ‘T_BudgetPositions .BUP_Jahr FROM T_BudgetPositions FULL JOIN T_AccountPlan ON T_AccountPlan.AP_UID = T_BudgetPositions.BUP_AP_UID AND T_AecountPlan.AP_SoftDeleteStatus = 1 WHERE (1-1) AND (T_BudgetPositions.BUP_SoftDeleteStatus = 1 OR T_BudgetPositions.BUP_SoftDeleteStatus IS NULL AND (T_AccountPlan.AP_SoftDeleteStatus = 1 OR T_AccountPlan.AP_SoftDeleteStatus IS NULL) Section 18.9: Recursive JOINS Recursive joins are often used to obtain parent-child data. In SQL. they are implemented with recursive common table expressions, for example: WITH RECURSIVE MyDescendants AS ( SELECT Name FROM People WHERE Name = “John Doe" UNION ALL SELECT People Name FROM People JOIN Mybescendants ON People.Name - MyDescendants. Parent SELECT * FROM MyDescendants Section 18.10: Basic explicit inner join Abaasic join (also called “inner join’) queries data from two tables, with their relationship defined in a join clause ‘The following example will select employees’ first names (FName) from the Employees table and the name of the department they work for (Name) from the Departments table: SELECT Enployees.FNane, Departments. Name FROM Employees JOIN Departments: Goolkicker-com - SQL Notes for Professionals, 74ON Employees DepartmentTd = Departments Id ‘This would return the following from the example database: Employees.FName Departments.Name James HR John HR Richard sales Section 18.11: Joining on a Subquery Joining a subquery is often used when you want to get aggregate data from a child/details table and display that along with records from the parent/header table. For example, you might want to get a count of child records, an average of some numeric column in child records, or the top or bottom row based on a date or numeric field. This, example uses aliases, which arguable makes queries easier to read when you have multiple tables involved. Here's what a fairly typical subquery join looks like. In this case we are retrieving all rows from the parent table Purchase Orders and retrieving only the first row for each parent record of the child table PurchaseOrderLineltems. SELECT po.Td, po.PODate, po.VendorName, po.Status, item.ItemNo, item. Description, item.Cost, item.Price FROM PurchaseOrders po LEFT JOIN ( SELECT 1.PurchaseOrderId, 1.TtemNo, 1.Description, 1.cost FROM PurchaseOrderLineItems 1 GROUP BY 1.PurchaseOrderTd, 1.ItenNo, 1.Description, 1.Cost AS iten ON item.PurchaseOrderId = po-Id L.Price, min(1.id) as Td L price Goolkicker-com - SQL Notes for Professionals, 5Chapter 19: UPDATE Section 19.1: UPDATE with data from another table The examples below fill in 2 PhoneNumber for any Employee who is also a Customer and currently does not have a phone number set in the Employees Table, (These examples use the Employees and Customers tables from the Example Databases.) Standard SQL Update using a correlated subquery, UPDATE Enployees. SET PhoneNunber SELECT .PhoneKunber FROM Customers HERE c.FName = Employees FName AND ¢.LName = Employees. Name) WHERE Employees.PhoneNumber IS NULL '$QL:2003 Update using MERGE: MERGE INTO Employees e USING Customers © on e.FName = ¢.Fnane AND e.LNane = c.LName AND e.PhoneNumber IS NULL WHEN MATCHED THEN UPDATE SET PhoneNunber = ¢.PhoneNunber SQLServer Update using INNER JOIN: UPDATE Employees ser PhoneNunber = ¢.PhoneNunber FROM Employees e INNER JOIN Custoners c ON e.Filane = ¢.FName AND e.LNane = c.LName WHERE PhoneNunber IS NULL Goolkicker-com - SQL Notes for Professionals, 76Section 19.2: Modifying existing values ‘This example uses the Cars Table from the Example Databases. UPDATE cars Set TotalCost = Totelcost + 102 WHERE Td = 3 or Td = 4 Update operations can include current values in the updated row. In this simple example the Totalcost is incremented by 100 for two rows: ‘+ The TotalCost of Car #3 is increased from 100 to 200 ‘© The TotalCost of Car #4 is increased from 1254 to 1354 Accolumn's new value may be derived from its previous value or from any other column's value in the same table or a joined table. Section 19.3: Updating Specified Rows This example uses the Cars Table from the Example Databases. UPDATE cars s Status = ‘READY’ WHERE d= 4 ‘This statement will set the status of the row of ‘Cars! with id 4 to "READY". WHERE clause contains a logical expression which is evaluated for each row. Ifa row fulfills the criteria, its value is updated, Otherwise, a row remains unchanged. Section 19.4: Updating All Rows ‘This example uses the Cars Table from the Example Databases. UPDATE Cars ‘Status = ‘READY’ ‘This statement will set the ‘status’ column of all rows of the 'Cars' table to "READY" because it does not have a WHERE clause to filter the set of rows. Section 19.5: Capturing Updated records Sometimes one wants to capture the records that have just been updated CREATE TABLE #TempUpdated(1D 1NT) Update TableNane ScT colt = 42 OUTPUT dnserted.1D INTO #Tempupdated WHERE Id > 50 Goolkicker-com - SQL Notes for Professionals, 7Chapter 20: CREATE Database Section 20.1: CREATE Database ‘A database is created with the following SQL command: CREATE DATABASE myDatabase This would create an empty database named myDatabase where you can create tables. Goolkicker-com - SQL Notes for Professionals,Chapter 21: CREATE TABLE Parameter Det tableName The name of the table Contains an ‘enumeration’ of all the colurnns that the table have. See Create a New Table for more details. ‘The CREATE TABLE statement is used create a new table in the database. A table definition consists of a list of columns, their types, and any integrity constraints. columns Section 21.1: Create Table From Select You may want to create a duplicate ofa table: CREATE TABLE Clonedémployees AS SELECT + FROM Employees You can use any of the other features of a SELECT statement to modify the data before passing it to the new table, ‘The colurnns of the new table are automatically created according to the selected rows. CREATE TABLE Modifiede SELECT Id, CONCAT( WHERE Id > 10 loyees AS. jane,” ",LName) AS FullName FROM Employees Section 21.2: Create a New Table ‘A bbasic Employees table, containing an ID, and the employee's first and last name along with their phone number can be created using CREATE TABLE Employees ( Id int identity(1,1) primary key not aul FName varchar(28) not null, UName varchar(28) not null. PhoneNunber varchar(1) not null ‘This example is specific to Transact-SOL CREATE TABLE creates a new table in the database, followed by the table name, Employees This is then followed by the list of column names and their properties, such as the ID Id int identity(1,1) not null Value Meaning. 1 the column's name. int is the data type. states that column will have auto generated values starting at 1 and incrementing by 1 for each identaty(1,1 YT) new row. prinary key states that all values in this column will have unique values ot null states that this colurnn cannot have null values Section 21.3: CREATE TABLE With FOREIGN KEY Below you could find the table Employees with a reference to the table Cities Goolkicker-com - SQL Notes for Professionals, 79CREATE TABLE Cities( CityID INT IDENTITY(1,1) NOT NULL Name VARCKAR(28) NOT NULL, Zip VARCHAR(1@) NOT NULL CREATE TABLE Employees EmployeeID INT IDENTITY (1,1) NOT NULL, FirstName VARCHAR(2@) NOT NULL, LastName VARCHAR(20) NOT NULL. PhoneNunber VARCHAR(1®) NOT NUL CityID INT FOREIGN KEY REFERENCES Cities(CityID) Here could you find a database diagram. Employees EmployeeiD FirstName Field PhoneNumber CityID The column cityT0 of table Employees will reference to the column CityT0 of table Cities. Below you could find the syntax to make this. CityID INT FOREIGN KEY REFERENCES Cities(CityID) Value Meaning cityrD Name of the column int type of the column, FOREIGN KEY Makes the foreign key (optional) REFERENCES Makes the reference Cities (CityID) to the table Cities column cityro Important: You couldn't make a reference to a table that not exists in the database. Be source to make first the table Cities and second the table Employees. If you do it vise versa, it will throw an error. Section 21.4: Duplicate a table To duplicate a table, simply do the following: CREATE TABLE newtable LIKE oldtable INSERT newtable SELECT » FROM oldtable Section 21.5: Create a Temporary or In-Memory Table PostgresQl and SQLite Goolkicker-com - SQL Notes for Professionals, 80To create a temporary table local to the session: CREATE TEMP TABLE MyTable(...) SQLServer To create a temporary table local to the session: CREATE TABLE #TempPhysical(...) To create a temporary table visible to everyone: CREATE TABLE ##TempPhysicalVisibleToEveryone(...) ‘To create an in-memory table: DECLARE @TenpMemory TABLE(...) Goolkicker-com - SQL Notes for Professionals,Chapter 22: CREATE FUNCTION ‘Argument Description function.name the name of function list_of_paramenters parameters that function accepts return_data_type type that function returs, Some SQL data type function body the code of function scalar_expression scalar value returned by function Section 22.1: Create a new Function CREATE FUNCTION FirstNord (input varchar(1#@®)) RETURNS varchar(1880) as BEGIN DECLARE Soutput varchar(1680) SET @output = SUBSTRING(@input, @, CASE CHARINDEX(” ", @input! WEN @ THEN LEN(@input) + 1 ELSE CHARINDEX(' *, @input: eno) RETURN @output END. This example creates a function named FirstWord, that accepts a varchar parameter and returns another varchar value, Goolkicker-com - SQL Notes for Professionals, #2Chapter 23: TRY/CATCH Section 23.1: Transaction In a TRY/CATCH This will rollback both inserts due to an invalid datetime: BEGIN TRANSACTION BEGIN TRY INSERT INTO dbo.Sale(Price, SaleDate VALUES (5.2, GETDATE(), 1) INSERT INTO dbo.Sale(Price, SaleDate VALUES (5.2, ‘not a date’, 1) COMMIT TRANSACTION END TRY BEGIN CATCH THROW ROLLBACK TRANSACTION END CATCH This will commit both inserts: BEGIN TRANSACTION BEGIN TRY INSERT INTO dbo.Sale(Price, SaleDate VALUES (5.2, GETDATE(), 1) INSERT INTO dbo.Sale(Price, SaleDate VALUES (8.2. 1) COMMIT TRANSACTION END TRY BEGIN CATCH THROM ROLLBACK TRANSACTION END CATCH Quantity) Quantity) Quantity) Quantity) Goolkicker-com - SQL Notes for Professionals, 3sChapter 24: UNION / UNION ALL UNION keyword in SQL is used to combine to SELECT statement results with out any duplicate. In order to use UNION and combine results both SELECT statement should have same number of column with same data type in same order, but the length of column can be different. Section 24.1: Basic UNION ALL query CREATE TABLE HR_EMPLOYEES PersonID int, LastName VARCHAR (3@) FirstName VARCHAR(38) Position VARCHAR(3®) LOYEES CREATE TABLE FINANCE. PersonID INT. LastName VARCHAR (3®) FirstName VARCHAR(38) Position VARCHAR(3®) Let's say we want to extract the names of all the managers from our departments, Using a UNION we can get all the employees from both HR and Finance departments, which hol manager 1e position of a SELECT FirstName, LastName FROM HR_ENPLOYEES WHERE Position = ‘manager’ UNION ALL SELECT FirstName, LastName FROM FINANCE_EMPLOYEES. WHERE Position = ‘manager’ ‘The UNTON statement removes duplicate rows from the query results. Since itis possible to have people having the same Name and position in both departments we are using UNION ALL, in order not to remove duplicates. Ifyou want to use an alias for each output column, you can just put them in the first select statement, as follows: SELECT FirstName as ‘First Name’, LastName as ‘Last Name: FROM HR_EMPLOYEES WHERE Position = ‘manager’ UNION ALL SELECT FirstNane, LastName FROM Goolkicker-com - SQL Notes for Professionals, 4FINANCE _EMPLO\ WHERE Position = ‘manager’ S Section 24.2: Simple explanation and Example In simple terms: ‘+ UNZON joins 2 result sets while removing duplicates from the result set ‘= UNION ALL Joins 2 result sets without attempting to remove duplicates (One mistake many people make is to use a UNION when they do not need to have the duplicates removed. ‘The additional performance cost against large results sets can be very significant, When you might need UNION Suppose you need to filter a table against 2 different attributes, and you have created separate non-clustered Indexes for each column, A UNTON enables you to leverage both indexes while still preventing duplicates. SELECT C1, €2, C3 UNION SELECT C1, C2, C3 FROM Table1 WHERE C2 = eParan2 {OM Table1 WHERE C1 = @Paramt ‘This simplifies your performance tuning since only simple indexes are needed to perform these queries optimally. You may even be able to get by with quite a bit fewer non-clustered indexes improving overall write performance against the source table as well When you might need UNTON ALL. Suppose you still need to filter a table against 2 attributes, but you do not need to fier duplicate records (either because it doesn't matter or your data wouldn't produce any duplicates during the union due to your data model design), SELECT C1 FROM Tablet UNION ALL SELECT C1 FROM Table This is especially useful when creating Views that join data that is designed to be physically partitioned across multiple tables (maybe for performance reasons, but still wants to roll-up records). Since the data is already split having the database engine remove duplicates adds no value and just adds additional processing time to the queries. Goolkicker-com - SQL Notes for Professionals, eChapter 25: ALTER TABLE ALTER command in SQL is used to modify colurnny/constraintin a table Section 25.1: Add Column(s) ALTER TABLE Employees ADD StartingDate date NOT NULL DEFAULT GetDate() Daveofeirth date NULL The above statement would add columns named Startingbate which cannot be NULL with default value as current date and bateofairth which can be NULL in Employees table. Section 25.2: Drop Column ALTER TABLE Employees DROP COLUMN salary ‘This will not only delete information from that column, but will drop the column salary from table employees(the column will no more exist) Section 25.3: Add Primary Key ALTER TABLE EMPLOYEES ADD pkEnployeeID PRIMARY KEY (ID) This will add a Primary key to the table Employees on the field Tp, Including more than one column name in the parentheses along with ID will create a Composite Primary Key. When adding more than one colurmn, the column names must be separated by commas. ALTER TABLE EMPLOYEES ADD pk-EmployeeTD PRIMARY KEY (ID, FName) Section 25.4: Alter Column ALTER TABLE Employees ALTER COLUMN StartingDs DATETIME NOT NULL DEFAULT (GETDATE() ‘This query will alter the column datatype of StartingDate and change it from simple date to datetime and set default to current date, Section 25.5: Drop Constraint ALTER TABLE Enployees DROP CONSTRAINT DefaultSalary ‘This Drops a constraint called DefaultSelary from the employees table definition. Note: Ensure that constraints of the column are dropped before dropping a column. Goolkicker-com - SQL Notes for Professionals, 36Chapter 26: INSERT INSERT data from another table using SELECT INSERT INTO Customers (FName, LName, PhoneNunber) SELECT FNane, LName, PhoneNunber FROM Employees Section 26. This example will insert all Employees into the Customers table. Since the two tables have different fields and you don’t want to move all the fields aver, you need to set which fields to insert into and which fields to select. The correlating field names don't need to be called the same thing, but then need to be the same data type. This, ‘example is assuming that the Id field has an Identity Specification set and will auto increment. Ifyou have two tables that have exactly the same field names and just want to move all the records over you can INSERT INTO Tablet SELECT * FROM Table2 Section 26.2: Insert New Row INSERT INTO Customers VALUES ("Zack", ‘Smith’, ‘zack@example.com’, "7249989942", * EMAIL’) This statement will insert a new row into the Customers table, Note that a value was not specified for the Td column, as it will be added automatically. However, all other column values must be specified. Section 26.3: Insert Only Specified Columns INSERT INTO Customers (FNeme, LName, Email, Preferredcontact VALUES (*Zack", "Smith’, ‘zack@exanple.com’, "EMALL ‘This statement will insert a new row into the Customers table, Data will only be inserted into the columns specified - Note that no value was provided for the PhoneNurber column, Note, however, that all columns marked as not null must be included. Section 26.4: Insert multiple rows at once Multiple rows can be inserted with a single insert command INSERT INTO tbi_nane (Fiel1, field2, f2e143) VALUES (1,2,3), (4.5.6), (7,89) For inserting large quantities of data (bulk insert) a exist ie same time, DBMS-specific features and recommendations MySQL - LOAD DATA INFILE MsSQL- BULK INSERT Goolkicker-com - SQL Notes for Professionals, 7Chapter 27: MERGE MERGE (often also called UPSERT for “update or insert’) allows to insert new rows or, if a row already exists, to Update the existing row. The point is to perform the whole set of operations atomically (to guarantee that the data remain consistent), and to prevent communication overhead for multiple SQL statements in a clienUserver system. Section 27.1: MERGE to make Target match Source MERGE INTO targetTable t USING sourceTable © ON t.PKID = s-PKID WHEN MATCHED AND NOT EXISTS ( SELECT s.ColumnA, s.Column®, s.Colunnt INTERSECT SELECT t.ColumnA, t.Colum®, s.Columnc THEN. UPDAT t.Colunna = s.Colunna t.Columna = 8.Colume £.Column¢ = s.Colunnc WHEN NOT MATCHED BY TARGET THEN INSERT (PKID, ColumnA, ColunnB, Columnc) VALUES (#.PKID, s.ColunnA, s.Colunn®, s.Columne: WHEN NOT MATCHED BY SOURCE THEN DELETE Note: The AND NOT EXISTS portion prevents updating records that haven't changed. Using the INTERSECT construct allows nullable columns to be compared without special handling. Section 27.2: MySQL: counting users by name ‘Suppose we want to know how many users have the same name. Let us create table users as follows create table usere( id int primary key auto_increment, name varchar(8) count int, unique key name(name) Now, we just discovered a new user named Joe and would like to take him into account. To achieve that, we need to determine whether there is an existing row with his name, and if so, update it to increment count; on the other hand, if there is no existing row, we should create it. MySQL uses the following syntax: Insert. on duplicate key update .... In this case’ insert into users(name, count) values (‘Joe’, 1) on duplicate key update count=counts1 Section 27.3: PostgreSQL: counting users by name ‘Suppose we want to know how many users have the same name. Let us create table users as follows: Goolkicker-com - SQL Notes for Professionals, 88create teble users: ad serial. fname varchar(8) unique, count int Now, we just discovered a new user named Joe and would like to take him into account. To achieve that, we need to determine whether there is an existing row with his name, and if so, update it to increment count; on the other hand, if there is no existing row, we should create it. PostgreSQL uses the following syntax: insert ... on conflict... do update .... In this case: insert into users(nane, count) velues('Joe’, 1 fon conflict (name) do update set count = users.count +1 Goolkicker-com - SQL Notes for Professionals, 8Chapter 28: cross apply, outer apply Section 28.1: CROSS APPLY and OUTER APPLY basics Apply will be used when when table valued function in the right expression. create a Department table to hold information about departments. Then create an Employee table which hold information about the employees. Please note, each employee belongs to a department, hence the Employee table has referential integrity with the Department table, First query selects data from Department table and uses CROSS APPLY to evaluate the Employee table for each record of the Department table. Second query simply joins the Department table with the Employee table and all the matching records are produced, SELECT FROM Department 0 CROSS APPLY ( ‘SELECT * FROM Employee E WHERE E.DepartmentID = D.DepartmentiD yA co SELECT = FROM Department D INNER JOIN Employee E ON D.DepartmentID = E.DepartmentID Ifyou look at the results they produced, itis the exact same result-set; How does it differ from a JOIN and how does ithelp in writing more efficient queries. ‘The first query in Script #2 selects data from Department table and uses OUTER APPLY to evaluate the Employee table for each record of the Department table. For those rows for which there is not a match in Employee table, those rows contains NULL values as you can see in case of row 5 and 6. The second query simply uses a LEFT OUTER JOIN between the Department table and the Employee table. As expected the query returns all rows from Department table; even for those rows for which there is no match in the Employee table. SELECT = FROM Department O OUTER APPLY ( ‘SELECT * FROM Employee E WHERE E.DepartmentID ~ D.DepartmentID A co SELECT = FROM Department D LEFT OUTER JOIN Employee E ON D.DepartmentID = E.DepartmentID co Even though the above two queries return the same information, the execution plan will be bit different. But cost wise there will be not much difference. Now comes the time to see where the APPLY operator is really required. In Script #3, | am creating a table-valued function which accepts DepartmentiD as its parameter and returns all the employees who belong to this department. The next query selects data from Department table and uses CROSS APPLY to join with the function Goolkicker-com - SQL Notes for Professionals, 90we created. It passes the Depart mentD for each row from the outer table expression (in our case Department table) and evaluates the function for each row similar to a correlated subquery. The next query uses the OUTER APPLY in place of CROSS APPLY and hence unlike CROSS APPLY which returned only correlated data, the OUTER APPLY returns non-correlated data as well, placing NULLs into the missing columns, CREATE FUNCTION dbo.fn_GetAllEmployeeOfADepartment (eDeptID AS int) RETURNS TABLE as RETURN ( SELECT FROM Employee E WHERE E.DepartmentID = eDepttD co SELECT FROM Department D CROSS APPLY dbo. fn_GetAllEmployeeOfADepartment (D.DepartmentID) so SELECT FROM Department D OUTER APPLY dbo. fn_GetAl1EmployeeOfADepartnent (D.DepartmentID) eo ‘So now if you are wondering, can we use a simple join in place of the above queries? Then the answer is NO, ifyou replace CROSS/OUTER APPLY in the above queries with INNER JOIN/LEFT OUTER JOIN, specify ON clause (something as 1=1) and run the query, you will get "The multi-part identifier "D.DepartmentiD" could not be bound." error. This is because with JOINS the execution context of outer query is different from the execution context ofthe function (or a derived table), and you can not bind a value/variable from the outer query to the function as a parameter. Hence the APPLY operator is required for such queries. Goolkicker-com - SQL Notes for Professionals, aChapter 29: DELETE The DELETE statementis used to delete records froma table. Section 29.1: DELETE all rows Omitting a WHERE clause will delete all rows from a table, DELETE FROM Employees ‘See TRUNCATE documentation for details on how TRUNCATE performance can be better because it ignores triggers and indexes and logs to just delete the data Section 29.2: DELETE certain rows with WHERE This will delete all rows that match the WHERE criteria DELETE FROM Employees WHERE FNane = ‘John’ Section 29.3: TRUNCATE clause Use this to reset the table to the condition at which it was created. This deletes all rows and resets values such as auto-increment, Italso doesn't log each individual row deletion. TRUNCATE TABLE Employees Section 29.4: DELETE certain rows based upon comparisons with other tables Itis possible to DELETE data from a table ifit matches (or mismatches) certain data in other tables Let's assume we want to DELETEdata from Source once its loaded into Target. DELETE FROM Source WHERE EXISTS ( SELECT 1 -- specific value in SELECT doesn't matter FROM Target Where Source.ID = Target.ID ) Most common RDBMS implementations (e.g. MySQL, Oracle, PostgresSQl, Teradata) allow tables to be joined during DELETE allowing more complex comparison in a compact syt ‘Adding complexity to original scenario, le’s assume Aggregate is bullt from Target once a day and does not contain the same ID but contains the same date, Let us also assume that we want to delete data from Source only after the aggregate is populated for the day. (On MySQL, Oracle and Teradata this can be done using: DELETE FROM Source WHERE Source.ID = TargetSchema.Target-1D AND TargetSchena. Target .Date = AggregateSchema.Aggregate.Dat In PostgreSQL use: Goolkicker-com - SQL Notes for Professionals, 92DELETE FROM Source USING TargetSchema. Target, AggregateScher WHERE Source.1D = TargetSchena. Target .1D AND TargetSchena. Target .Datadate = AggregateSchena. Aggregate Aggbate Aggregate This essentially results in INNER JOINs between Source, Target and Aggregate. The deletion is performed on Source when the same IDs exist in Target AND date present in Target for those IDs also exists in Aggregate. ‘Same query may also be written (on MySQL, Oracle, Teradata) as: DELETE Source FROM Source, TargetSchena.Target, AggregateSchena. Aggregate WHERE Source-ID = TargetSchema.Target.1D AND TargetSchena Target .DataDate = AggregateSchema. Aggregate AggDate Explicit joins may be mentioned in Delete statements on some RDBMS implementations (e.g. Oracle, MySQL) but ‘not supported on all platforms (e.g, Teradata does not support them) ‘Comparisons can be designed to check mismatch scenarios instead of matching ones with all syntax styles (observe NOT EXISTS below) DELETE FROM Source WHERE NOT EXISTS ( SELECT 1 -- specific value in SELECT doesn't matter FROM Target. Where Source.ID = Target.ID ) Goolkicker-com - SQL Notes for Professionals, 8Chapter 30: TRUNCATE ‘The TRUNCATE statement deletes all data from a table. This is similar to DELETE with no filter, but, depending on the database software, has certain restrictions and optimizations. Section 30.1: Removing all rows from the Employee table TRUNCATE TABLE Enployee Using truncate table is often better then using DELETE TABLE as it ignores all the indexes and triggers and just removes everything. Delete table is a row based operation this means that each row is deleted. Truncate table is a data page operation the entire data page is reallocated. If you have a table with a million rows it will be much faster to truncate the table than it would be to use a delete table statement, ‘Though we can delete specific Rows with DELETE, we cannot TRUNCATE specific rows, we can only TRUNCATE all the records at once, Deleting All rows and then inserting a new record will continue to add the Auto incremented Primary key value from the previously inserted value, where as in Truncate, the Auto Incremental primary key value will also get reset and starts from 1 Note that when truncating table, no foreign keys must be present, otherwise you will get an error. Goolkicker-com - SQL Notes for Professionals, 4Chapter 31: DROP Table Section 31.1: Check for existence before dropping MySQL version = 3.19 DROP TABLE IF EXISTS MyTable PostgreSQL version = 8.x DROP TABLE IF EXISTS MyTable SQL Server version = 2005 If Existe(Select * From Information_Schema Tables Where Table_Schena = ‘dbo And Table_Nane = ‘HyTable") Drop Table dbo.MyTable SQLite Version = 3.0 DROP TABLE IF EXISTS myTable; Section 31.2: Simple drop Drop Table MyTable; Goolkicker-com - SQL Notes for Professionals,Chapter 32: DROP or DELETE Database Section 32.1: DROP Database Dropping the database is a simple one-liner statement. Drop database will delete the database, hence always ‘ensure to have a backup of the database if required, Below is the command to drop Employees Database DROP DATABASE [dbo] . [Employees] Goolkicker-com - SQL Notes for Professionals, 96Chapter 33: Cascading Delete Section 33.1: ON DELETE CASCADE ‘Assume you have a application that administers rooms, ‘Assume further that your application operates on a per client basis (tenant) You have several clients. So your database will contain one table for clients, and one for rooms. Now, every client has N rooms. This should mean that you have a foreign key on your room table, referencing the client table. ALTER TABLE dbo.T_Room WITH CHECK ADD CONSTRAINT FK_T_Room.T_Client FOREIGN KEY(RM_CLI_ID) REFERENCES dbo.T.Client (CLI_I0) 60 ‘Assuming a client moves on to some other software, youll have to delete his data in your software. But if you do DELETE FROM T_Client WHERE CLILID = x hen he still has rooms. ‘Then youll get a foreign key violation, because you can't delete the client Now you'd have write code in your application that deletes the client's rooms before it deletes the client. Assume further that in the future, many more foreign key dependencies will be added in your database, because your application's functionality expands. Horrible. For every modification in your database, you'll have to adapt your application's code in N places. Possibly youll have to adapt code in other applications as well (e.g. interfaces to other systems). There is a better solution than doing it in your code. You can just add ON DELETE CASCADE to your foreign key. ALTER TABLE dbo.T_Room -- WITH CHECK -- SQL-Server can specify WITH CHECK/WITH NOCHECK ‘ADD CONSTRAINT FK_T_Room_T Client FOREIGN KEY(RM_CLI_ID) REFERENCES dbo.T.Client (CLI_ID) ON DELETE CASCADE Now you can say DELETE FROM T_Client WHERE CLILID = x and the rooms are automagically deleted when the client is deleted. Problem solved - with no application code changes. One word of caution: In Microsoft SQL-Server, this won't work if you have a table that references itselfs. So if you try to define a delete cascade on a recursive tree structure, like this: IF NOT EXISTS (SELECT » FROM sys. foreign_keys WHERE object_id = (03JECT_ID(N’ [dbo] . [FK_T_FNS_Navigation_T_FMS_Navigation]") AND parent_object_id = (03JECT_ID(N' [dbo] . [T_FHS_Navigation}")) ALTER TABLE [dbo]. [T_FNS Navigation] NITH CHECK ADD CONSTRAINT FK_T_FNS_Navigation_T_FWS Navigation] FOREIGN KEY ((NA_NA_UID| REFERENCES [dbo] .[T_FHS_Navigation] ((NA_UID]) ON DELETE CASCADE Goolkicker-com - SQL Notes for Professionals, 7co S (SELECT * FROM sys. foreign_keys WHERE object_id (03JECT_ID(N’ [dbo] . [FK_T_FNS_Navigation_T_FMS_Navigation]") AND parent_object_id = (03JECT_ID(N’ [dbo] . [T_FHS_Navigation}" )) ALTER TABLE [dbo] .(T_FNS Navigation] CHECK CONSTRAINT [FK_T_FAS_Navigation_T_FS_Navigation eo IF exis’ it won't work, because Microsoft-SQL-server doesn't allow you to set a foreign key with ON DELETE CASCADE on a recursive tree structure. One reason for this is, that the tree is possibly cyclic, and that would possibly lead to a deadlock. PostgreSQL on the other hand can do this; the requirement is that the tree is non-cyclc Ifthe tree is cyclic, youll get a runtime error. In that case, youll just have to implement the delete function yourselfs. Aword of cauti ‘This means you can't simply delete and re-insert the client table anymore, because if you do this it will delete all entries in "T_Room"...(no non-delta updates anymore) Goolkicker-com - SQL Notes for Professionals, 98Chapter 34: GRANT and REVOKE Section 34.1: Grant/revoke privileges GRANT SELECT, UPDATE ON Employees To User, User? Grant User and User? permission to perform SELECT and UPDATE operations on table Employees. REVOKE SELECT, UPDATE ON Employees FROM Usert, User2; Revoke from User and User? the permission to perform SELECT and UPDATE operations on table Employees. Goolkicker-com - SQL Notes for Professionals,Chapter 35: XML Section 35.1: Query from XML Data Type DECLARE @xmLIN XML = ‘ ' SELECT t.col.value(*../eMain’, ‘varchar(1@)') (Header) t.col.value("@nane', 'VARCHAR(25)") [name] t-col.value(‘evalue', 'VARCHAR(25)') [Value] FROM @xmlIn.nodes('//TableData/aaa/row') AS t (col) Results Header name Value First. a First First Second Second Second Third Third Third Goolkicker-com - SQL Notes for Professionals, 700Chapter 36: Primary Keys Section 36.1: Creating a Primary Key CREATE TABLE Employees ( Id int NOT NULL, PRIMARY KEY (Td) ‘This will create the Employees table with ‘ld’ as its primary key. The primary key can be used to uniquely identify the rows of a table. Only one primary key is allowed per table, ‘Akkey can also be composed by one or more fields, so called composite key, with the following syntax: CREATE TABLE EMPLOYEE ( e1_i¢ INT, e2_i¢ INT PRIMARY KEY (e1_id, e2_id) Section 36.2: Using Auto Increment Many databases allow to make the primary key value automatically increment when a new key is added. This ensures that every key is different. Mysou. CREATE TABLE Employees ( Id int NOT NULL AUTO_INCREMENT, PRIMARY KEY (Td) PosteresoL. CREATE TABLE Employees ( Id SERIAL PRIMARY KEY SOL Server CREATE TABLE Employees Id ant NOT NULL IDENTITY PRIMARY KEY (Td) soLite CREATE TABLE Employees ( Td INTEGER PRIMARY KEY Goolkicker-com - SQL Notes for Professionals, iorChapter 37: Indexes Indexes are a data structure that contains pointers to the contents of a table arranged in a specific order, to help the database optimize queries. They are similar to the index of book, where the pages (rows of the table) are indexed by their page number. Several types of indexes exist, and can be created on a table. When an index exists on the columns used in 2 ‘query’s WHERE clause, JOIN clause, or ORDER BY clause, it can substantially improve query performance. Section 37.1: Sorted Index Ifyou use an index that is sorted the way you would retrieve it, the SELECT statement would not do additional sorting when in retrieval. CREATE INDEX ix.seoreboard.score ON scoreboard (score DESC) When you execute the query SELECT * FROM scoreboard ORDER BY score DESC ‘The database system would not do adcltional sorting, since it can do an index-lookup in that order Section 37.2: Partial or Filtered Index ‘SQL Server and SQLite allow to create indexes that contain not only a subset of columns, but also a subset of rows. Consider a constant growing amount of orders with order_state_id equal to finished (2), and a stable amount of orders with order_state_id equal to started (1) If your business make use of queries like this: SELECT 4¢, conment FROM orders WHERE order_stateid - 1 AND product_id = @sone_value Partial indexing allows you to limit the index, including only the unfinished orders: CREATE INDEX Started_Orders ON orders (product_id) WHERE order_state_id 7 This index will be smaller than an unfiltered index, which saves space and reduces the cost of updating the index. Section 37.3: Creating an Index CREATE INDEX ix.cors_employee_id ON Cars (Enployeets) This will create an index for the column Employeeld in the table Cars. This index will improve the speed of queries asking the server to sort or select by values in Employeeld, such as the following: SELECT * FROM Cars WHERE EmployeeId = 1 Goolkicker-com - SQL Notes for Professionals, 102‘The index can contain more than 1 column, as in the following; CREATE INDEX ix_cars_e_c_o_ids ON Cars (EnployeeId, Card, OwnerIo In this case, the index would be useful for queries asking to sort or select by all included columns, ifthe set of conditions is ordered in the same way. That means that when retrieving the data, it can find the rows to retrieve Using the index, instead of looking through the full table. For example, the following case would utilize the second index; SELECT * FROM Cars WHERE EmployeeId = 1 Order by Carid DESC Ifthe order differs, however, the index does not have the same advantages, asin the following; SELECT * FROM Cars WHERE OwnerTd = 17 Order by CarTd DESC ‘The index is not as helpful because the database must retrieve the entire index, across all values of Employeeld and CarlD, in order to find which items have OwnerTd = 17. (The index may still be used; it may be the case that the query optimizer finds that retrieving the index and filtering ‘on the Owner id, then retrieving only the needed rows is faster than retrieving the full table, especially ifthe table is, large.) Section 37.4: Dropping an Index, or Disabling and Rebuilding it DROP INDEX ix_e _-employee_id ON Cars We can use command DROP to delete our index. In this example we will DROP the index called Dx. cars employee id on the table Cars. This deletes the index entirely, and if the index is clustered, will remove any clustering, It cannot be rebuilt without recreating the index, which can be slow and computationally expensive. As an alternative, the index can be disabled: ALTER INDEX ix_cars_employee_id ON Cars DTSABLE ‘This allows the table to retain the structure, along with the metadata about the index. Critically, this retains the index statistics, so that it is possible to easily evaluate the change. If warranted, the index can then later be rebuilt, instead of being recreated completely; ALTER INDEX ix_cars_employee_id ON Cars REBUILD Section 37.5: Clustered, Unique, and Sorted Indexes Indexes can have several characteristics that can be set either at creation, or by altering existing indexes. CREATE CLUSTERED INDEX ‘xclustenployee_td ON Enployees(Enployeetd, Email ‘The above SQL statement creates a new clustered index on Employees. Clustered indexes are indexes that dictate the actual structure of the table; the table itself is sorted to match the structure of the index. That means there can be at most one clustered index on a table, Ifa clustered index already exists on the table, the above statement will Goolkicker-com - SQL Notes for Professionals, 08fail. (Tables with no clustered indexes are also called heaps.) CREATE UNIQUE TNDEX ug_customers_enail ON Customers (Email) This will create an unique index for the column Email in the table Customers. This index, along with speeding up ‘queries like a normal index, will also force every email address in that column to be unique. If a row is inserted or updated with a non-unique Email value, the insertion or update will, by default, fall. CREATE UNIQUE INDEX ix_ei_desc ON Customers(EnployeeID) ‘This creates an index on Customers which also creates a table constraint that the EmployeeID must be unique. (This will fall if the column is not currently unique - in this case, if there are employees who share an ID.) CREATE INDEX 4x_eid_dese ON Customers(EmployeeID Desc) ‘This creates an index that is sorted in descending order. By default, indexes (in MSSQL server, at least) are ascending, but that can be changed, Section 37.6: Rebuild index Over the course of time B-Tree indexes may become fragmented because of updating/deleting/inserting data. In ‘SQLServer terminology we can have internal (index page which is half empty ) and external (logical page order doesnt correspond physical order) Rebuilding index is very similar to dropping and re-creating it We can re-build an index with ALTER INDEX index_nane REBUILD By default rebuilding index is offline operation which locks the table and prevents DML against it, but many RDBMS allow online rebuilding. Also, some DB vendors offer alternatives to index rebuilding such as REORGANIZE (SQLServer) or COALESCE/SHRINK SPACE(Oracle). Section 37.7: Inserting with a Unique Index UPDATE Customers 1 = "richarde1236example.com” WHERE id = 1 ‘This will fail ifan unique index is set on the Email column of Customers. However, alternate behavior can be defined for this case: UPDATE Customers 1 = "richarde1236example.com” WHERE id = 1 ON DUPLICATE KEY Goolkicker-com - SQL Notes for Professionals, 104

You might also like

• SQL - Notes For Professionals PDF

  

  No ratings yet

  SQL - Notes For Professionals PDF

  166 pages
• 100 Pages On SQL PDF

  

  No ratings yet

  100 Pages On SQL PDF

  166 pages
• SQL Notes For Professional

  

  No ratings yet

  SQL Notes For Professional

  166 pages
• SQL Detailed Notes For Professionals 1672765219

  

  No ratings yet

  SQL Detailed Notes For Professionals 1672765219

  166 pages
• SQL PDF

  

  No ratings yet

  SQL PDF

  166 pages
• SQL - Notes For Professionals

  

  No ratings yet

  SQL - Notes For Professionals

  166 pages
• SQL Notes ?

  

  No ratings yet

  SQL Notes ?

  161 pages
• SQL Deep Guide

  

  No ratings yet

  SQL Deep Guide

  236 pages
• SQL Notes

  

  No ratings yet

  SQL Notes

  220 pages
• SQL PDF

  

  100% (13)

  SQL PDF

  221 pages
• SQL Complete Guide

  

  100% (1)

  SQL Complete Guide

  220 pages
• SQL Theory With Query

  

  No ratings yet

  SQL Theory With Query

  11 pages
• Structured Query Language (SQL)

  

  No ratings yet

  Structured Query Language (SQL)

  66 pages
• Learn SQL - Know Everything About SQL

  

  100% (1)

  Learn SQL - Know Everything About SQL

  220 pages
• Lesson 3 Introduction To SQL

  

  No ratings yet

  Lesson 3 Introduction To SQL

  66 pages
• SQL

  

  No ratings yet

  SQL

  9 pages
• SQL Manuscript

  

  No ratings yet

  SQL Manuscript

  154 pages
• SQLNotes

  

  100% (1)

  SQLNotes

  223 pages
• SQL Commands

  

  No ratings yet

  SQL Commands

  13 pages
• (Structured: A Useng Chang Is by

  

  No ratings yet

  (Structured: A Useng Chang Is by

  24 pages
• All Mysql Queries Cheat Sheet

  

  No ratings yet

  All Mysql Queries Cheat Sheet

  8 pages
• Lec07 - SQL (Cont.)

  

  No ratings yet

  Lec07 - SQL (Cont.)

  79 pages
• 70+ SQL Interview Questions

  

  No ratings yet

  70+ SQL Interview Questions

  19 pages
• SQL For Everyone (Definitive Guide)

  

  No ratings yet

  SQL For Everyone (Definitive Guide)

  10 pages
• Xid-32778727 1

  

  No ratings yet

  Xid-32778727 1

  55 pages
• SQL Basics

  

  No ratings yet

  SQL Basics

  8 pages
• SQL Query

  

  No ratings yet

  SQL Query

  14 pages
• Structured Query Language

  

  No ratings yet

  Structured Query Language

  13 pages
• 9 - SQL Notes

  

  No ratings yet

  9 - SQL Notes

  9 pages
• SQL Guide for Data Analysts

  

  No ratings yet

  SQL Guide for Data Analysts

  11 pages
• Basic SQL Commands

  

  No ratings yet

  Basic SQL Commands

  10 pages
• SQL Basics: Commands and Queries

  

  No ratings yet

  SQL Basics: Commands and Queries

  46 pages
• MySQL DISTINCT, LIMIT, IN, and JOIN Guide

  

  No ratings yet

  MySQL DISTINCT, LIMIT, IN, and JOIN Guide

  12 pages
• Chapter 7 SQL Practical

  

  No ratings yet

  Chapter 7 SQL Practical

  5 pages
• SQL

  

  100% (1)

  SQL

  100 pages
• SQL Essentials for Beginners

  

  No ratings yet

  SQL Essentials for Beginners

  5 pages
• Lab1 Asmamaw-2

  

  No ratings yet

  Lab1 Asmamaw-2

  19 pages
• SQL DBMS

  

  No ratings yet

  SQL DBMS

  76 pages
• SQL Revision

  

  No ratings yet

  SQL Revision

  20 pages
• SQL Theory Notes

  

  No ratings yet

  SQL Theory Notes

  29 pages
• 7 Introduction To SQL PART 2

  

  No ratings yet

  7 Introduction To SQL PART 2

  58 pages
• Beautified SQL Guide

  

  No ratings yet

  Beautified SQL Guide

  13 pages
• Format - Magic - Doc - 2025 07 01T10 15 02

  

  No ratings yet

  Format - Magic - Doc - 2025 07 01T10 15 02

  5 pages
• What Is SQL?: SQL Is A Standard Language For Accessing and Manipulating Databases

  

  No ratings yet

  What Is SQL?: SQL Is A Standard Language For Accessing and Manipulating Databases

  5 pages
• SQL Notes

  

  No ratings yet

  SQL Notes

  29 pages
• SQL Notes by Apna College

  

  No ratings yet

  SQL Notes by Apna College

  29 pages
• SQL For Everyone

  

  No ratings yet

  SQL For Everyone

  11 pages
• SQL Essentials for Database Users

  

  No ratings yet

  SQL Essentials for Database Users

  48 pages
• Learning SQL-Alan - Beaulieu PDF

  

  No ratings yet

  Learning SQL-Alan - Beaulieu PDF

  337 pages
• Table of Contents

  

  No ratings yet

  Table of Contents

  4 pages
• Introduction To Database

  

  No ratings yet

  Introduction To Database

  57 pages
• SQL

  

  No ratings yet

  SQL

  3 pages
• PRACTICALS DBMS FYCS Nep-3

  

  No ratings yet

  PRACTICALS DBMS FYCS Nep-3

  33 pages
• SQL Checklist Keywords Explanation and Examples

  

  No ratings yet

  SQL Checklist Keywords Explanation and Examples

  6 pages
• Structured Query Language (SQL) : Prepared By: Raquel Ofreneo

  

  No ratings yet

  Structured Query Language (SQL) : Prepared By: Raquel Ofreneo

  27 pages
• If Else Statements

  

  No ratings yet

  If Else Statements

  3 pages
• Intelligent Braking System: International Journal of Research

  

  No ratings yet

  Intelligent Braking System: International Journal of Research

  3 pages
• Solar-Powered Bucket Elevator Thesis

  

  100% (1)

  Solar-Powered Bucket Elevator Thesis

  46 pages
• Jawaharlal Nehru Technological University Kakinada

  

  No ratings yet

  Jawaharlal Nehru Technological University Kakinada

  53 pages
• Intelligent Car Braking System With Collision Avoidance and ABS

  

  No ratings yet

  Intelligent Car Braking System With Collision Avoidance and ABS

  5 pages
• Mouli

  

  No ratings yet

  Mouli

  9 pages