To write and execute SQL queries for the nested and join queries.
SQL Joins and Nested Queries
A join clause is used to combine rows from two or more tables, based on a related column between them.
Here are the different types of the JOINs in SQL:
- (INNER) JOIN: Returns records that have matching values in both tables.
- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
- RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
- FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.
- CARTESIAN JOIN or CROSS JOIN: There is a join for each row of one table to every row of another table. This usually happens when the matching column or WHERE condition is not specified.
- SELF JOIN: A table is joined to itself, meaning each row of the table is joined with itself and all other rows depending on certain conditions.
Example Query: INNER JOIN
SELECT students_detail.name, student1_courses.course FROM students_detail INNER JOIN student1_courses ON students_detail.rollno =
student1_courses.rollno;
Nested Queries
A Subquery, or nested query, can be defined as a query within another query. It is embedded in clauses such as WHERE, HAVING, or FROM. Subqueries can be used with SELECT, UPDATE, INSERT, and DELETE statements along with expression operators like =, >, <=, >=, and LIKE. They must be enclosed in parentheses.
Example Nested Query
UPDATE customers11 SET salary=10000 WHERE age
IN (SELECT age FROM customers123 WHERE age > 25);
Comments
Post a Comment