Id | FName | LName | DeptId |
---|---|---|---|
1 | James | Smith | 3 |
2 | John | Johnson | 4 |
Id | Name |
---|---|
1 | Sales |
2 | Marketing |
3 | Finance |
4 | IT |
*
is the wildcard character used to select all available columns in a table.FROM
. This effect applies to all tables the query accesses through its JOIN
clauses.Employees
table:Id | FName | LName | DeptId |
---|---|---|---|
1 | James | Smith | 3 |
2 | John | Johnson | 4 |
Employee
table, followed by just the Name
field in the Departments
table:Id | FName | LName | DeptId | Name |
---|---|---|---|---|
1 | James | Smith | 3 | Finance |
2 | John | Johnson | 4 | IT |
*
is avoided in production code where possible, as it can cause a number of potential problems including:SELECT <columns> FROM <table>
.SELECT * FROM orders JOIN people ON people.id = orders.personid ORDER BY displayname
- if a column column called displayname
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 name where the person name is intended because the new column is the first of that name returned, and so on.*
, Bearing The Above Warning In Mind?*
is fine as a shorthand when performing manual queries against the database for investigation or prototype work.tablealias.*
over just *
where possible).EXISTS
, such as SELECT A.col1, A.Col2 FROM A WHERE EXISTS (SELECT * FROM B where A.ID = B.A_ID)
, 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.LIKE
statement on a column of DateTime
datatype in SQL Server?DATEPART
function to extract portions of dates. It should also make your queries more explicit about what you're seeking to achieve:MONTH
and YEAR
, but I prefer DATEPART
for consistency since it can access all components of a datetime
)datetime
s as having any kind of string format. Treating them as strings is one of the largest sources of errors we encounter.Like
operator (for some reason) you have to convert the DateTime
column into a varchar
.