SQL NOT CONTAINS:
SQL NOT CONTAINS is a type of SQL operator that is used to retrieve data from a database table where a certain value is not present. This operator is the opposite of the SQL CONTAINS operator, which is used to retrieve data where a certain value is present.
For example, let's say we have a table called "employees" with the following columns: "id", "name", "age", and "department". If we want to retrieve all the employees who do not work in the "IT" department, we can use the SQL NOT CONTAINS operator as follows:
SELECT * FROM employees WHERE department NOT CONTAINS 'IT';
This query will return all the rows from the "employees" table where the value in the "department" column is not "IT".
The SQL NOT CONTAINS operator can also be used with wildcard characters. For example, if we want to retrieve all the employees whose name does not start with the letter "J", we can use the following query:
SELECT * FROM employees WHERE name NOT CONTAINS 'J%';
This query will return all the rows from the "employees" table where the value in the "name" column does not start with the letter "J".
Another example: Let's say we have a table called "products" with the following columns: "product_id", "product_name", "product_price". If we want to retrieve all the products whose product_name does not contain the letter "a" we can use the following query:
SELECT * FROM products WHERE product_name NOT CONTAINS '%a%';
It is worth noting that the SQL NOT CONTAINS operator can also be used in combination with other operators, such as the AND and OR operators. For example, if we want to retrieve all the employees who do not work in the "IT" department and are older than 30, we can use the following query:
SELECT * FROM employees WHERE department NOT CONTAINS 'IT' AND age > 30;
This query will return all the rows from the "employees" table where the value in the "department" column is not "IT" and the value in the "age" column is greater than 30.
In conclusion, the SQL NOT CONTAINS operator is a powerful tool for retrieving data from a database table where a certain value is not present. It can be used with wildcard characters, in combination with other operators, and in a variety of different scenarios.
SQL JOINS:
SQL JOINS are used to combine data from two or more tables in a relational database. The tables are joined based on a common column, known as the join key. There are several types of SQL JOINS, including: INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.
INNER JOIN: The INNER JOIN keyword selects records that have matching values in both tables. If a record in the first table does not have a matching record in the second table, that record will be excluded from the result set.
SELECT * FROM table1
INNER JOIN table2
ON table1.column = table2.column;
OUTER JOIN: The OUTER JOIN keyword selects records that have matching values in one table or the other. If a record in the first table does not have a matching record in the second table, that record will be included in the result set with NULL values in the second table's columns.
SELECT * FROM table1
LEFT OUTER JOIN table2
ON table1.column = table2.column;
LEFT JOIN: The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.
SELECT * FROM table1
LEFT JOIN table2
ON table1.column = table2.column;
RIGHT JOIN: The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.
SELECT * FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;
FULL OUTER JOIN: The FULL OUTER JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records.
SELECT * FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
SQL SUBQUERIES:
SQL subqueries are a powerful feature of SQL that allow you to embed one SELECT statement within another. A subquery is a SELECT statement that is nested inside another statement, such as a SELECT, INSERT, UPDATE, or DELETE statement.
A subquery can be used in various parts of a SQL statement, including the SELECT, FROM, WHERE, and HAVING clauses. The subquery is enclosed in parentheses, and it returns a result set that is used by the outer query.
Here is an example of a subquery in the SELECT clause:
SELECT column1, (SELECT column2 FROM table2 WHERE table2.column3 = table1.column3)
FROM table1;
In this example, the subquery retrieves the value of column2 from table2 where column3 has the same value as column3 in table1. The result of the subquery is used to retrieve values from column1 of table1.
A subquery can also be used in the FROM clause, also known as a subquery in the FROM clause, or derived table. This is useful when the subquery returns a result set that you want to treat as a table.
SELECT column1
## Popular questions
1. What is the SQL NOT CONTAINS operator and when is it used?
- The SQL NOT CONTAINS operator is used to retrieve data from a database table where a certain value is not present. It is the opposite of the SQL CONTAINS operator, which is used to retrieve data where a certain value is present.
2. How can we retrieve all the employees who do not work in the "IT" department using the SQL NOT CONTAINS operator?
- To retrieve all the employees who do not work in the "IT" department using the SQL NOT CONTAINS operator, we can use the following query:
SELECT * FROM employees WHERE department NOT CONTAINS 'IT';
3. Can we use wildcard characters with the SQL NOT CONTAINS operator?
- Yes, we can use wildcard characters with the SQL NOT CONTAINS operator. For example, to retrieve all the employees whose name does not start with the letter "J", we can use the following query:
SELECT * FROM employees WHERE name NOT CONTAINS 'J%';
4. How can we use the SQL NOT CONTAINS operator in combination with other operators?
- We can use the SQL NOT CONTAINS operator in combination with other operators, such as the AND and OR operators. For example, to retrieve all the employees who do not work in the "IT" department and are older than 30, we can use the following query:
SELECT * FROM employees WHERE department NOT CONTAINS 'IT' AND age > 30;
5. Are there any alternatives to the SQL NOT CONTAINS operator?
- An alternative to the SQL NOT CONTAINS operator is the NOT LIKE operator. It can be used to retrieve data from a database table where a certain value is not present. However, NOT LIKE doesn't support the use of wildcard characters. For example, to retrieve all the employees whose name does not start with the letter "J", we can use the following query:
SELECT * FROM employees WHERE name NOT LIKE 'J%';
Note: The above examples are based on the assumption that the database engine you are using supports the NOT CONTAINS operator, which is not supported by all database engines.
### Tag
Operators
