Skip to main content

Posts

SQL SELECT INTO Statement With Exxample

SQL SELECT INTO StatementSELECT INTO copies data from one table into a new table.SELECT INTO creates a new table located in the default filegroup. The SQL SELECT INTO syntax The general syntax is:
SELECT column-namesINTO new-table-nameFROM table-nameWHEREEXISTS(SELECT column-nameFROM table-nameWHERE condition)

The new table will have column names as specified in the query.
SQL SELECT INTO Example

SQL WHERE EXISTS Statement with Example

SQL WHERE EXISTS StatementWHERE EXISTS tests for the existence of any records in a subquery.EXISTS returns true if the subquery returns one or more records.EXISTS is commonly used with correlated subqueries. The SQL EXISTS syntax The general syntax is:
SELECT column-namesFROM table-name WHEREEXISTS(SELECT column-nameFROM table-nameWHERE condition)
SQL EXISTS Example

SQL WHERE ANY, ALL Clause With Example

SQL WHERE ANY, ALL ClauseANY and ALL keywords are used with a WHERE or HAVING clause.ANY and ALL operate on sub-queries that return multiple values.ANY returns true if any of the sub-query values meet the condition.ALL returns true if all of the sub-query values meet the condition. The SQL WHERE ANY and ALL syntax The general ANY syntax is:
SELECT column-names FROM table-nameWHERE column-name operator ANY(SELECT column-nameFROM table-name WHERE condition)

The general ALL syntax is: SELECT column-namesFROM table-nameWHERE column-name operator ALL(SELECT column-nameFROM table-nameWHERE condition) SQL ANY Example

SQL Subqueries With Example

SQL Sub-queriesA sub-query is a SQL query within a query.Sub-queries are nested queries that provide data to the enclosing query.Sub-queries can return individual values or a list of recordsSub-queries m The SQL sub-query syntax There is no general syntax; sub-queries are regular queries placed inside parenthesis.
Sub-queries can be used in different ways and at different locations inside a query:
Here is an sub-query with the IN operator
SELECT column-namesFROM table-name1WHERE value IN(SELECT column-nameFROM table-name2 WHERE condition)
Sub-queries can also assign column values for each record:
SELECT column1 =(SELECT column-name FROM table-name WHERE condition),column-namesFROM table-name WHERE condition
SQL Sub-query Examples

SQL UNION Clause With Example

SQL UNION ClauseUNION combines the result sets of two queries.Column data types in the two queries must match.UNION combines by column position rather than column name

The SQL UNION syntax
The general syntax is:
SELECT column-namesFROM table-nameUNION SELECT column-namesFROM table-name
SQL UNION Examples

SQL Self JOIN or NATURAL JOIN With Example

SQL Self JOINA self JOIN occurs when a table takes a 'selfie'.A self JOIN is a regular join but the table is joined with itself.This can be useful when modeling hierarchies.They are also useful for comparisons within a table.
The SQL Self JOIN syntax
The general syntax is:
SELECT column-namesFROM table-name T1 JOIN table-name T2 WHERE condition


T1 and T2 are different table aliases for the same table  SQL Self JOIN Examples

SQL FULL JOIN Statement with Example

SQL FULL JOIN Statement FULL JOIN returns all matching records from both tables whether the other table matches or not. FULL JOIN can potentially return very large data-sets. FULL JOIN and FULL OUTER JOIN are the same.
The SQL FULL JOIN syntax
The general syntax is:
SELECT column-namesFROM table-name1 FULLJOIN table-name2 ON column-name1 = column-name2 WHERE condition

The general FULL OUTER JOIN syntax is:
SELECT column-namesFROM table-name1 FULLOUTERJOIN table-name2 ON column-name1 = column-name2WHERE condition SQL FULL JOIN Examples