Page 97 - CITS - Computer Software Application -TT

COMPUTER SOFTWARE APPLICATION - CITS

Syntax

SELECT columns
FROM table1
FULL JOIN table2 ON table1.column_name = table2.column_name;
When performing a join, it’s essential to specify the columns on which you want to join the tables. These columns
should have compatible data types or can be explicitly converted to compatible data types.
Here’s a simple example using two hypothetical tables, “orders” and “customers,” to demonstrate an INNER JOIN:
SELECT orders.order_id, customers.customer_name
FROM orders

INNER JOIN customers ON orders.customer_id = customers.customer_id;
This query retrieves the order IDs and customer names for orders that have matching customer IDs in both the
“orders” and “customers” tables.

Sub Queries

A subquery in SQL is essentially a query nested within another query, commonly found within the WHERE clause
of the main SQL query. Here are some fundamental rules and guidelines for using subqueries:

1 Purpose of Subqueries: Subqueries are employed to retrieve data that will be used by the main query for
filtering, comparison, or as a part of calculations.
2 Location: Subqueries are typically placed within parentheses and inside the WHERE clause of the main
query. They can also be used in other clauses like SELECT, FROM, or HAVING when necessary.
3 Comparison Operators : Subqueries often use comparison operators such as =, <, >, <=, >=, or IN to relate
the results of the subquery with the main query.
4 Single-Row or Multi-Row Results : Subqueries can return either a single value or a set of values (single-row
or multi-row results). The choice depends on the specific use case and the operator used.

5 Subquery Types : Subqueries can be categorized into various types, including scalar subqueries (returning a
single value), single-row subqueries (returning one row), and multi-row subqueries (returning multiple rows).
6 Alias and Correlation : When using subqueries in the SELECT clause, it’s often necessary to assign an alias
to the subquery. Additionally, correlated subqueries refer to the main query’s tables, allowing for more complex
relationships between the subquery and the main query.

84

CITS : IT&ITES - Computer software application - Lesson 18 - 36