SQL Tutorial: Select, Update & Delete Syntax
Select Statements
General Syntax
- SELECT [column], [column], [column] FROM [tablename] WHERE [column] = [value]
Select All Columns
- SELECT * FROM [tablename]
Limit # of Records Returned – MSSQL
- SELECT TOP [number] * FROM [tablename]
Limit # of Records Returned – SQL
- SELECT * FROM [tablename] LIMIT [# from], [# to]
- this one works well for pagination. You can select results 0 – 30, then 31 to 60, etc.
- SELECT * FROM [tablename] LIMIT [number]
- use this one if you just want to cut off the number of records returned
Sum A Field By Status ID
- SELECT sum([column]), status_id FROM [tablename] WHERE [column] = [value] GROUP BY status_id
Count A Field
- SELECT count([column]) FROM [tablename] WHERE [column] = [value]
Sort By A Field Ascending or Descending
- SELECT [column], [column] FROM [tablename] WHERE [column] = [value] ORDER BY [column] DESC, [column] ASC
Complex Where Values
- SELECT [column], [column] FROM [tablename] WHERE ([column] = [value] AND [column] = [value]) OR ([column] = [value] AND [column] = [value])
Select From Result Subset
- SELECT [column], [column] FROM [tablename] WHERE [column] IN ([value], [value], [value])
Select From Select Subset
- SELECT [column] FROM [tablename] WHERE [column] IN (SELECT [column] FROM [tablename])
Inner Join Select All – records must exist in table B and table A matching on conditions
- SELECT * FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value]
Left Join Select All – records must exist in table A
- SELECT * FROM [tablename] A LEFT OUTER JOIN [tablename] B on A.[column] = B.[column] WHERE B.[column] = [value]
Inner Join Select All From One Table, Select Fields From Another
- SELECT A.*, B.[column], B.[column] FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value]
Unions – results and columns must match in both SQL statements
- SELECT A.*, B.[column], B.[column] FROM [tablename] A INNER JOIN [tablename] B ON A.[column] = B.column WHERE A.[column] = [value] UNION SELECT C.*, D.[column], D.[column] FROM [tablename] C INNER JOIN [tablename] D ON C.[column] = D.column WHERE C.[column] = [value]
Delete Statements
Always do a select statement first to make sure the records you’re about to delete are the ones you want. Also consider any triggers that may be fired as a result of deleting these records.
General Syntax
- DELETE FROM [tablename] WHERE [column] = [value]
Check Yourself
- SELECT * FROM [tablename] WHERE [column] = [value]
- Now replace the text in red with the text in blue below to delete the records you found
- DELETE FROM [tablename] WHERE [column] = [value]
Update Statements
Make sure you do a select statement before attempting to update records to ensure you only update the records you intended. Also consider any triggers that may be fired as a result of updating these records.
General Syntax
- UPDATE [tablename] SET [column]=[value], [column]=[value], [column]=[value] WHERE [column]=[value]
Check Yourself
- SELECT * FROM [tablename] WHERE [column] = [value]
- Replace the blue section with what’s in red below, then add the columns and values you want to update as seen in green below
- UPDATE [tablename] SET [column]=[value], [column]=[value], [column]=[value] WHERE [column]=[value]
