This page contains useful T-SQL for SQL Server
|
Table of Contents
|
DataTypes
System supplied DataTypes
CHAR / VARCHAR (n<8,000) VARCHAR(max) = 2^31 non-unicode
NCHAR / NVARCHAR (n<4000) NVARCHAR(max) = 2^31
BINARY / VARBINARY (n<8000 bytes) VARBINARY(max) = 2^31 bytes
TINYINT (0 to 255)
SMALLINT (-32,768 to 32,767)
INT (-2,147,483,648 to 2,147,483,647)
BIGINT (-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807)
DECIMAL (precision, scale) eg. DECIMAL (9,2) = 9,999,999.99
SMALLDATETIME (01/01/1900 to 06/06/2079) down to a minute
DATETIME (01/01/1753 to 31/12/9999) down to 3.3 milliseconds
TIMESTAMP
UNIQUEIDENTIFIER
SQL_VARIANT
XML
User-defined DataTypes
Add Rule
CREATE RULE RuleName
AS @ParameterName = 'Value'
sp_bindrule 'RuleName', 'user-defined datatype'
Drop Rule
DROP RULE RuleName
Add Default
CREATE DEFAULT DefaultName
AS 'Value'
sp_bindefault 'DefaultName', 'user-defined datatype'
Drop Default
DROP DEFAULT DefaultName
Object Names
ServerName.DatabaseName.SchemaName.ObjectName
Default schema names: dbo, .
CAST function
This function converts one datetype to another
Select CAST(one.fieldname AS VARCHAR) FROM table1 one
CONVERT function
This function converts datetime and numeric values
SELECT CONVERT(VARCHAR(12), datefieldname, 103) (103 = dd/mm/yyyy)
SELECT CONVERT (VARCHAR(10), 1.234567, 2)
Dates
IsDate
This function determines if valid date
SELECT ISDATE(fieldname1)
DateFormat
SET DATEFORMAT dmy (mdy, dmy, ymd,myd.dym)
DateName and DatePart
DateName returns string, whereas DatePart returns number
SELECT DATENAME (mm, GETDATE()) = December
SELECT DATEPART (mm, GETDATE()) = 12
SELECT DAY (CURRENT_TIMESTAMP) = 15
SELECT MONTH (CURRENT_TIMESTAMP) = 12
SELECT YEAR (CURRENT_TIMESTAMP) = 2008
Following datepart supported - year, yy, yyyy, qq,q, month, mm, m, dayofyear, dy, y, day, dd, d, week, wk, ww, hour, hh, minute, mi, n, second, ss, s, millisecond, ms
Datetime
CURRENT_TIMESTAMP or GETDATE () - returns current date as datetime
DATEADD (datepart, interval, date) eg. DATEADD(day, 1, GETDATE())
DATEDIFF (datepart, startdate, enddate)
Null
Coalesce
This function returns an alternativefield when row in fieldname1 is null or alternative value when row in alternativefield is null
SELECT COALESCE(fieldname1, alternativefield, 'alternativevalue')
Presenting tabular output as a comma delimited string
IsNull
This function returns alternative value when row in fieldname1 is null
SELECT ISNULL(fieldname1, 'alternativevalue')
Functions
IsNumeric
This function determines if numeric datatype
SELECT ISNUMERIC(fieldname1)
Mathematical
ABS(n) - absolute number
CEILING (n) - rounds up number
EXP (n) - e to the power n
FLOOR (n) - rounds down number
LOG (n) - natural logarithm of n
LOG10 (n) - base-10 logarithm of n
ROUND (n, places) - rounds n to number of places
SIGN (n) - returns -1,0,1 depending on sign of n
PI(), SQRT(n), SQUARE(n), RAND(0-1)
ACOS(Cosine), ASIN(sine),ATAN(tangent), ATN2(x,y)
COS(radians), COT (radians), SIN(radians), TAN(radians)
x % y - modulus = remainder of x / y
String
ASCII: ASCII(character), CHAR(0-255)
Unicode: UNICODE(character), NCHAR(0-65535)
CHARINDEX(substring, string, [,position]]), PATINDEX(substring, fieldname1)
LEFT(string, n), RIGHT(string, n)
REPLACE(string, search, replace), STUFF(string, start, length, character)
REPLICATE(string, n), SPACE(n)
REVERSE(string)
SUBSTRING(string, start, length)
NULLIF(string1,string2)
LEN(string)
Concatenation: string1 + string2
TRIM(character FROM string)
LTRIM(string), RTRIM(string)
UPPER(string), LOWER(string)
Aggregate
These functions counts the number of rows
SELECT COUNT(*) FROM table1
SELECT COUNT(DISTINCT fieldname) FROM table1
These queries counts the number of rows grouped by fieldname1
SELECT one.fieldname1, COUNT(*) AS countColumn FROM table1 one
GROUP BY one.fieldname1
SELECT one.nameID, MAX(one.name), COUNT(*) as countColumn FROM table1 one
GROUP BY one.nameID
SELECT two.fieldID, two.fieldname1, subquery.countColumn FROM table2 two
INNER JOIN (SELECT one.nameID, COUNT(*) AS countColumn FROM table1 one
GROUP BY one.nameID) subquery ON two.fieldID = subquery.nameID
SELECT one.fieldname1, COUNT(*) AS countColumn FROM table1 one
GROUP BY one.fieldname1
HAVING COUNT(*) = VALUE
This query generates a summary row for each group
SELECT one.fieldname1, one.fieldname2, COUNT(*) AS countColumn FROM table1 one
GROUP BY one.fieldname1, one.fieldname2 WITH ROLLUP
This query generates all possible summary rows and grand total for each group
SELECT one.fieldname1, one.fieldname2, COUNT(*) AS countColumn FROM table1 one
GROUP BY one.fieldname1, one.fieldname2 WITH CUBE
This query generates all possible summary rows and grand total for each group, with 1 if null results or 0 if no null results
SELECT one.fieldname1, one.fieldname2, COUNT(*) AS countColumn FROM table1 one
GROUPING(one.fieldname1) as gp1, GROUPING (one,fieldname2) as gp2
GROUP BY one.fieldname1, one.fieldname2 WITH CUBE
Other aggregate functions
AVG(fieldname)
COUNT(fieldname)
MAX(fieldname)
MIN(fieldname)
STDEV(fieldname)
SUM(fieldname)
VAR(fieldname)
Window
Aggregates over specified number of rows determined by PARTITION BY clause
SELECT one.fieldname,
MIN(one.fieldname) OVER (PARTITION BY one.fieldname2) AS windowfunctionname1,
AVG(one.fieldname) OVER () AS windowfunctionname2
MIN(one.fieldname) OVER (PARTITION BY one.fieldname2, one.fieldname3) AS windowfunctionname3,
FROM table1 one
This query ranks aggregated rows
SELECT one.fieldname,
ROW_NUMBER() OVER (ORDER BY one.fieldname3) AS Rank1,
ROW_NUMBER() OVER (PARTITION BY one.fieldname2 ORDER BY one.fieldname3) AS Rank2,
RANK () OVER (PARTITION BY one.fieldname2 ORDER BY one.fieldname3) AS Rank3,
DENSE_RANK () OVER (PARTITION BY one.fieldname2 ORDER BY one.fieldname3) AS Rank4,
one.fieldname3
FROM table1 one
ROW_NUMBER = 1,2,3,4, …n, RANK = 1,2,2,4, …n, DENSE_RANK = 1,2,2,3, …n
Transactions
Autocommit
Disable autocommit (so that each statement is not treated as a transaction)
SET IMPLICIT_TRANSACTIONS ON
Enable autocommit (so that each statement is treated as a transaction = Default mode)
SET IMPLICIT_TRANSACTIONS OFF
Isolation Level
Set isolation level (Read Committed = Default)
SET TRANSACTION ISOLATION LEVEL
{READ COMMITTED|READ UNCOMMITTED|REPEATABLE READ|SERIALIZABLE}
Transactions
Begin Transaction
BEGIN TRAN[SACTION] TransactionName
WITH MARK [Description] - adds transaction note in log
Add Transaction Savepoint
SAVE TRAN[SACTION] SavepointName
Abort Transaction
ROLLBACK TRAN[SACTION] TransactionName
ROLLBACK TO SAVEPOINT SavepointName
End Transaction
COMMIT TRAN[SACTION] TransactionName
Database Structure
Databases
Create Database
CREATE DATABASE DatabaseName
ON
(NAME='FileName',
FILENAME = 'FilePath/FileName.mdf', :Default path = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data'
SIZE = n, :Default = MB
MAXSIXE = n, :Default = MB
FILEGROWTH = n) :Default = MB, %
LOGON
(NAME='FileNameLog',
FILENAME = 'FilePath/FileNamelog.ldf', :Default path = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\mssql\data'
SIZE = n, :Default = MB
MAXSIXE = n, :Default = MB
FILEGROWTH = n) :Default = MB, %
Alter Database
ALTER DATABASE DatabaseName
MODIFY FILE
(NAME='FileName',
SIZE = n)
Drop Database
DROP DATABASE DatabaseName
Backup and Restore Database
EXEC sp_addumpdevice 'DISK' 'BackupDeviceName' 'FilePathName.bak'
BACKUP DATABASE DatabaseName
To BackupDeviceName
WITH
{DIFFERENTIAL}
{DESCRIPTION = 'Message'}
{STATS}
BACKUP LOG DatabaseName
To BackupDeviceName
WITH
{DIFFERENTIAL}
{DESCRIPTION = 'Message'}
{STATS}
RESTORE DATABASE DatabaseName
FROM BackupDeviceName
WITH
DBO_ONLY
NORECOVERY
STATS
RESTORE LOG DatabaseName
FROM BackupDeviceName
WITH
DBO_ONLY
NORECOVERY
STATS
RESTORE LOG DatabaseName WITH RECOVERY
EXEC sp_dboption DatabaseName, 'dbo use only', 'false'
Detach and reattach Database
sp_detach_db DatabaseName
sp_attach_db DatabaseName, 'FilePathName'
Tables
Create Table
CREATE TABLE TableName (
fieldid INT IDENTITY (start, increment) PRIMARY KEY, :ROWGUIDCOL
fieldname1 VARCHAR(n) NOT NULL,
FOREIGN KEY REFERENCES TableName2 (fieldname2)
ON UPDATE NO ACTION {CASCADE|NO ACTION|SET NULL|SET DEFAULT}
ON DELETE CASCADE, {CASCADE|NO ACTION|SET NULL|SET DEFAULT}
fieldname2 VARCHAR(n) DEFAULT 'Value',
fieldname3 AS fieldname1 * fieldname2,
CONSTRAINT constraintName1 NOT NULL,
CONSTRAINT constraintName2 CHECK (fieldname2 IN ('Value1', 'Value2', Value…n)),
fieldname3 VARCHAR(n),
CONSTRAINT constraintName3 PRIMARY KEY (fieldid),
CONSTRAINT constraintName4 FOREIGN KEY (fieldname1, fieldname2) REFERENCES table1 (fieldname3, fieldname4),
CONSTRAINT constraintName5 UNIQUE (fieldname1, fieldname2),
CONSTRAINT constraintName6 CHECK (fieldname3 = UPPER(fieldname1)))
Add NONCLUSTERED after PRIMARYKEY if you want that key not to be clustered
Alter Table
ALTER TABLE TableName
ALTER fieldName
VARCHAR(n) NUll
ALTER TABLE TableName
WITH NO CHECK
ADD fieldname4 VARCHAR(n)
CONSTRAINT constraintName7 CHECK (fieldname4 IN ('Value1', 'Value2', Value…n))
ADD constraintName8 CHECK (fieldname4 = LTRIM (fieldname4))
ADD constraintName9 DEFAULT 'Value' FOR fieldname1
ADD CONSTRAINT constraintName10 PRIMARY KEY (fieldname1)
ADD CONSTRAINT constraintName11 FOREIGN KEY (fieldname1) REFERENCES tablename2 (fieldname2)
ALTER TABLE TableName
DROP CONSTRAINT constraintName
Disable Constraint
ALTER TABLE TableName
NOCHECK
CONSTRAINT constraintName
Enable Constraint
ALTER TABLE TableName
CHECK
CONSTRAINT constraintName
Drop Table
DROP TABLE TableName
Get all tables in database
SELECT table_name FROM information_schema.tables
WHERE table_type = 'Base table'
Indexes
Create Index
CREATE INDEX indexname ON table1
(fieldname1, fieldname2)
Create unique clustered index IndexName on table1
(fieldname1) with fillfactor = 100
Explore Index
DBCC SHOWCONTIG (tablename, indexname)
Alter Index
ALTER INDEX { IndexName|ALL}
ON table1
{ REBUILD|DISABLE|REORGANIZE}
Rebuild Index
DBCC DBREINDEX (tablename, indexname, FillfactorValue)
Remove Index
DROP INDEX indexname ON table1
Views
Create View
CREATE VIEW ViewName_vw
WITH ENCRYPTION This encrypts the view hiding underlying T-SQL used to create the view
WITH SCHEMABINDING This prevents alteration to referenced objects eg tables
VIEW METADATA Allow views to appear as tables to client API
AS
SELECT one.fieldname1, one.fieldname2 FROM table1 one
WHERE one.fieldid = 'VALUE'
WITH CHECK OPTION This only allows updates and inserts in a view which match the WHERE criteria
NB.
Can not use ORDER BY when creating view,
Indexed views or schema-bound user-defined functions need WITH SCHEMABINDING
Tables and user-defined functions must be two part named eg schema.tablename and from the same database as the view
Alter views
ALTER VIEW ViewName_vw
SELECT one.fieldname1, one.fieldname2, one.fieldname3 FROM table1 one
WHERE one.fieldid = 'VALUE'
Create Index
CREATE UNIQUE CLUSTERED INDEX indexName
ON viewName (viewFieldname1, viewFieldname2)
Drop View
DROP VIEW ViewName_vw
Data Structure
Select
This query returns an alias field from table1
SELECT one.fieldname1 AS "First Field" FROM table1 one
WHERE one.fieldname3 = 'Value'
ORDER BY one.fieldname1 ASC, one.fieldname2 DESC
This query selects unique rows
SELECT DISTINCT (*) FROM table1
Select Case
This query uses the simple CASE statement to output value depending on value in fieldname2
SELECT one.fieldname1,
CASE one.fieldname2
WHEN 'value1' THEN 'Value x'
WHEN 'Value2' THEN 'Value y'
ELSE 'Value z'
END AS fieldname3
FROM table1 one
This query uses the searched CASE statement to output value depending on value in fieldname2
SELECT one.fieldname1,
CASE
WHEN one.fieldname2 = 'Value1' THEN 'Value x'
WHEN one.fieldname2 = 'Value2' THEN 'Value y'
ELSE 'Value z'
End AS fieldname3
FROM table1 one
Union
This query unions the results of 2 queries, removing duplicate rows
SELECT one.fieldname1 AS unionfieldname FROM table1 one
UNION
SELECT two.fieldname1 FROM table2 two
ORDER BY unionfieldname
This query unions the results of 2 queries, without removing duplicate rows
SELECT one.fieldname1 AS unionfield FROM table1 one
UNION ALL
SELECT two.fieldname2 FROM table2 two
Except
This query subtracts the results of 2 queries
SELECT one.fieldname1 AS exceptfieldname FROM table1 one
EXCEPT
SELECT two.fieldname1 FROM table2 two
Intersect
This query finds the common rows between 2 queries
SELECT one.fieldname1 AS exceptfieldname FROM table1 one
INTERSECT
SELECT two.fieldname1 FROM table2 two
Delete
This query deletes all rows from a table
TRUNCATE TABLE table1
This query deletes one row filtered by fieldid
DELETE FROM table1
WHERE fieldid = 'Value1'
This two queries deletes rows in table1 where joined rows not value1 in table 2
DELETE FROM (SELECT * FROM table1 one WHERE one.fieldid NOT IN
(SELECT two.fieldid FROM table2 two WHERE two.fieldname1 = 'Value1'))
DELETE FROM table1
FROM table1 one INNER JOIN table2 two ON one.fieldid = two.fieldid
WHERE two.fieldname1 = 'Value1'
Insert
This query inserts a row into table one
INSERT INTO Table1
(Fieldname1, Fieldbname2, Fieldname3, Fieldname..n)
VALUES
('Value1, 'Value2', 'Value3','Value..n') //DEFAULT, NULL
This query inserts rows from table2 into a table1
INSERT INTO table1
SELECT two.fieldname1, three.fieldname2
FROM table2 two
INNER JOIN table3 three ON two.fieldid = three.fieldid
This query inserts a row into a view
INSERT INTO (SELECT Fieldname1, Fieldname2, Fieldname3,Fieldname..n FROM Table1)
(FieldOne, FieldTwo, FieldThree, Field..n)
VALUES
('Value1, 'Value2', 'Value3','Value..n')
This query inserts row into a new table
SELECT *
into newtable1 one
from oldtable2 two
where two.fieldname = 'Value'
This allows insertion into identity field
SET identity_insert TableName ON
Append
This query appends records, filtered by field, from table two to table one
INSERT INTO table1
SELECT * from table2
WHERE table2.FilterField IN
(
'Value1',
'Value2',
'Value3'
)
Update
This query updates a record in a table
UPDATE TableName
SET fieldname1 = 'Value', fieldname2 = 'Value'
WHERE fieldid = 'idvalue'
This query updates multiple records from a linked source table
UPDATE DestinationTable
SET DestinationField = s.SourceField
FROM DestinationTable d
INNER JOIN SourceTable s ON d.fieldid = s.fieldid
WHERE d.otherfield = 'VALUE'
This query updates rows using a linked source table
UPDATE DestinationTable
SET DestinationField = d.DestinationField + s.sourceField
FROM DestinationTable d
INNER JOIN SourceTable s ON d.fieldid = s.fieldid
Joins
This query uses a cross join to output all possible combinations of rows from 2 tables
SELECT * FROM table1 CROSS JOIN table2
This query uses an inner join between 2 tables
SELECT one.fieldname1, two.fieldname2 FROM table1 one
INNER JOIN table2 two ON one.fieldid = two.fieldid
This query uses a left outer join between 2 tables, returning all rows from table1
SELECT one.fieldname1, two.fieldname2 FROM table1 one
LEFT OUTER JOIN table2 two ON one.fieldid = two.fieldid
This query uses a right outer join between 2 tables, returning all rows from table2 with no entry in table1
SELECT one.fieldname1, two.fieldname2 FROM table1 one
RIGHT OUTER JOIN table2 two ON one.fieldid = two.fieldid
WHERE one.fieldid IS NULL
This query uses a full outer join between 2 tables, returning all rows from both tables
SELECT one.fieldname1, two.fieldname2 FROM table1 one
FULL OUTER JOIN table2 two ON one.fieldid = two.fieldid
Temporary Table
This query inserts rows into a temporary table
SELECT one.fieldname1, one.fieldname2
INTO #tempTableName
FROM table1 one
DROP TABLE #tempTableName
Derived Table
This query uses a derived table to find rows in table 1 that meet 2 values in table 3
SELECT fieldname1 FROM table1 one
INNER JOIN
(SELECT two.fieldid two FROM table2 two INNER JOIN three.fieldname3 ON two.fieldid = three.fieldid
WHERE three.fieldname4 = 'Value1') DERIVEDTABLE1
ON one.fieldid on DERIVEDTABLE1.fieldid
INNER JOIN
(SELECT two.fieldid two FROM table2 two INNER JOIN three.fieldname3 ON two.fieldid = three.fieldid
WHERE three.fieldname4 = 'Value2') DERIVEDTABLE2
ON one.fieldid on DERIVEDTABLE2.fieldid
Nested Subquery
This query uses a subquery within the SELECT Clause
SELECT one.fieldname1, one.fieldname2,
(SELECT COUNT(*) FROM table2 two WHERE two.fieldid = one.fieldid) AS FieldCount
FROM table1 one
This query uses a subquery in the FROM clause
SELECT one.fieldname1, one.fieldname2 FROM (SELECT * FROM table1 WHERE fieldname3 ='Value) one
This query uses a subquery in the JOIN clause
SELECT one.PrimaryID, subquery.Information FROM table1 one
LEFT OUTER join (SELECT DISTINCT one.PrimaryID, two.Information FROM table1 one
INNER JOIN table2 two ON one.ForeignID = two.PrimaryID) AS subquery ON one.PrimaryID = subquery.ID
This query uses a subquery in the WHERE clause
SELECT one.fieldname1, one.fieldname2 FROM table1 one
WHERE one.fieldid IN (SELECT two.fieldid FROM table2 two WHERE two.fieldname = 'Value')
This query uses a subquery in the ORDER BY clause
SELECT one.fieldname1, one.fieldname2 FROM table1 one
ORDER BY (SELECT COUNT(*) FROM table2 two WHERE two.fieldid = one.fieldid)
This query uses a subquery in an INSERT clause
INSERT INTO Table2 (Fieldname1, Fieldname2, Fieldname3, Fieldname..n)
(SELECT Fieldname1, Fieldname2, Fieldname3, Fieldname..n FROM Table1
WHERE fieldid = 'Value')
This query uses a subquery in a DELETE clause
DELETE
FROM (SELECT * FROM table1 one WHERE one.fieldname = 'Value') secondone
WHERE secondone.fieldid IS NOT NULL
This query uses a subquery in an UPDATE clause to update one row in table1
UPDATE TableName1 one
SET fieldname1 = (SELECT two.fieldname1 FROM table2 two WHERE two.fieldname2 = 'Value')
WHERE one.fieldname2 = 'Value'
This query uses WITH clause for the subquery
WITH subquery1 AS (SELECT one.fieldid AS fieldid, one.fieldname1 AS subqueryfield1 FROM table1 one), subquery2 AS (SELECT two.fieldid, two.fieldname1 AS subqueryfield2 FROM table2 two)
SELECT subqueryfield1 FROM subquery1
INNER JOIN table3 ON subquery1.fieldid = table3.fieldID
WHERE table3.fieldID = 'Value'
Correlated Subquery
This query uses a correlated subquery in a SELECT clause
SELECT one.fieldname1
(SELECT MIN(two.fieldname2 FROM table2 two WHERE two.fieldid = one.fieldid)
FROM table1 one
This query uses a correlated subquery in a WHERE clause
SELECT one.fieldname1, one.fieldname2 FROM table1 one
WHERE one.fieldname2 = (SELECT MIN(two.fieldname3) FROM table2 two WHERE two.fieldid = one.fieldid)
Recursive Query
This queries recursive and hierarchical data
WITH recursivequery
(level, fieldid, fieldparentid, fieldname1, fieldname2) AS
(SELECT 1, parent.fieldid, parent.fieldparentid, parent.fieldname1, parent.fieldname2
FROM Table1 parent WHERE parent.fieldparentid IS NULL
UNION ALL
SELECT parent.level+1, child.fieldid, child.fieldparentid, child.fieldname1, child.fieldname2
FROM recursivequery parent, Table1 child
WHERE child.fieldparentid = parent.fieldparentid)
SELECT level, fieldid, fieldparentid, fieldname1, fieldname2
FROM recursivequery
Distributed Query
Link Servers
EXEC sp_addlinkdserver
@server = 'LINKEDSERVERNAME'
@srvproduct = 'SQLServer OLEDB Provider'
@provider = 'SQLOLEDB'
@datasrc ='LINKEDDATABASENAME'
Verify linked server exists
EXEC sp_linkedservers
Provide current username details
EXEC sp_addlinkedsrvlogin LINKEDSERVERNAME, True
Provide different username details
EXEC sp_addlinkedsrvlogin LINKEDSERVERNAME, False, 'sa', 'username', 'password'
Run query
BEGIN DISTRIBUTED TRAN
SELECT fieldname1, fieldname2 FROM linkedservername.linkeddatabasename.dbo.table1
COMMIT TRAN
Run stored procedure - turn remote procedure calls on
EXEC sp_serveroption 'LINKEDSERVERNAME', 'rpc', TRUE
EXEC sp_serveroption 'LINKEDSERVERNAME', 'rpc out', TRUE
EXEC linkedservername.linkeddatabasename.dbo.storedprocedurename
Drop linked server
EXEC sp_dropserver 'LINKEDSERVERNAME'
Pass-Through Query
SELECT fieldname1 FROM OPENQUERY('LINKEDSERVERNAME', 'SELECT fieldid, fieldname1 from databasename.dbo.table1') ls
INNER JOIN table2 two ON ls.fieldId = two.fieldId
SELECT fieldname1 FROM OPENROWSET('SQLOLEDB', 'DatabaseName'; 'username'; 'password', 'SELECT fieldid, fieldname1 from databasename.dbo.table1') ls
INNER JOIN table2 two ON ls.fieldId = two.fieldId
Data Filter
Exists
This query returns rows in table 1 where row exists in table 2
SELECT one.fieldid, one.fieldname1 FROM table1 one
WHERE EXISTS (SELECT * FROM table2 two WHERE two.fieldID = one.fieldid)
This query returns rows in table 1 where row does not exist in table 2
SELECT one.fieldid, one.fieldname1 FROM table1 one
WHERE NOT EXISTS (SELECT * FROM table2 two WHERE two.fieldID = one.fieldid)
IN
This query returns rows in table 1 where fieldid equals one of values
SELECT one.fieldid, one.fieldname1 FROM table1 one
WHERE one.fieldid IN (Value1, Value2, Value..n)
This query returns rows in table 1 where fieldid exists in table 2
SELECT one.fieldid, one.fieldname1 FROM table1 one
WHERE one.fieldid IN (SELECT two.fieldid FROM table2 two)
Between
This query returns rows in table 1 where fieldid is between 2 values
SELECT one.fieldid, one.fieldname1 FROM table1 one
WHERE one.fieldid BETWEEN minValue AND maxValue
Like
This query returns rows in table 1 where fieldname contains substring
SELECT one.fieldid, one.fieldname1 FROM table1 one
WHERE one.fieldname LIKE '%substring%'
This query returns rows in table 1 where fieldname does not contains substring
SELECT one.fieldid, one.fieldname1 FROM table1 one
WHERE one.fieldname NOT LIKE '%substring%'
This query returns rows in table 1 where fieldname contains %
SELECT one.fieldid, one.fieldname1 FROM table1 one
WHERE one.fieldname LIKE '%/%%' ESCAPE '/'
% = wildcard, _ = wildcard including newlines, [abc] matches set of characters, [^abc] excludes set of characters
Null
This turns off ANSI_NULLS
SET ANSI_NULLS OFF
…. WHERE fieldname1 = NULL
…. WHERE fieldname1 <> NULL
This select query filters on a null value
select tn.fieldname1 from TableName tn
where tn.fieldname2 IS NULL
This select query filters on a non null values
select tn.fieldname1 from TableName tn
where tn.fieldname2 IS NOT NULL
