In MongoDB, the $orderBy query modifier sorts the results of a query in ascending or descending order.
$orderBy accepts a document that specifies the field to sort, and the sort order. The sort order can be either 1 for ascending or -1 for descending.
$orderBy has been deprecated in the mongo shell since v3.2. Use the cursor.sort() method instead.
In MongoDB, the $nin query operator selects the documents where the field’s value is not in the specified array or where the field doesn’t exist.
In MongoDB, the $in query operator selects the documents where the value of a field equals any value in the specified array.
The $in query operator is not to be confused with the $in aggregation pipeline operator, which returns a boolean indicating whether a specified value is in the array.
In SQL, you can join three tables or more by adding another join after the first one.
You can also run nested joins by specifying one join as the join condition for another.
If you ever find yourself in the situation where you’re about to execute a stored procedure, but you suddenly catch yourself. You wonder “How many columns does this thing return? Which tables? Does it query a remote server?”
The good news is there are several ways to get this information before you run the query. Let’s look at them.
A LEFT SEMI JOIN is kind of a half-join. It returns any distinct values that are returned by both the query on the left and right sides of the query.
However, when using T-SQL in SQL Server, if you try to explicitly use LEFT SEMI JOIN in your query, you’ll probably get the following error:
A LEFT ANTI SEMI JOIN is a type of join that returns only those distinct rows in the left rowset that have no matching row in the right rowset.
But when using T-SQL in SQL Server, if you try to explicitly use LEFT ANTI SEMI JOIN in your query, you’ll probably get the following error:
In SQL, the UNION clause concatenates the results of two queries into a single result set.
You can use the UNION clause with or without the ALL argument:
UNION ALL – Includes duplicates.UNION – Excludes duplicates.Some RDBMSs also accept UNION DISTINCT, which is the equivalent to UNION. That is, it excludes duplicates.
Below are some basic examples to demonstrate how it works.
In SQL, the AVG() function is an aggregate function that returns the average of all values in a given expression.
It can also be used to return the average of all distinct (unique) values in an expression.
The expression must be numeric (it cannot be character string, bit string, or datetime).
Below are some basic examples to demonstrate how it works.