Database Testing Part -2
Hello readers,
Today, let us try to know more about database testing, we have discussed earlier why database testing is important, how to test it, and much more. here..
Today, QA interviews getting harder these days and they are also asking some questions on databases like SQL, MongoDB, MYSQL, and many more.
In the database world, SQL Joins are like matchmakers. Imagine you have information about your customers in one list and details about their purchases in another. SQL Joins help connect these lists based on common elements, creating a complete picture of who bought what.
SQL Joins
SQL JOIN clause is used to query and access data from multiple tables by establishing logical relationships between them. It can access data from multiple tables simultaneously using common key values shared across different tables.
We can use SQL JOIN with multiple tables. It can also be paired with other clauses, the most popular use will be using JOIN with WHERE clause to filter data retrieval.
I love the concept, though, so let’s see if we can make it work. Assume we have the following two tables. Table A is on the left, and Table B is on the right. We’ll populate them with four records each.
id name id name
-- ---- -- ----
1 Pirate* 1 Rutabaga
2 Monkey 2 Pirate*
3 Ninja* 3 Darth Vader
4 Spaghetti 4 Ninja*
5 NewMan null null
6 Lion null null
Let’s join these tables by the name field in a few different ways and see if we can get a conceptual match to those nifty Venn diagrams.
Types of Joins
Types of JOIN in SQL
There are many types of Joins in SQL. Depending on the use case, you can use different type of SQL JOIN clause. Here are the frequently used SQL JOIN types:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL JOIN
The INNER JOIN keyword selects all rows from both the tables as long as the condition is satisfied. This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e value of the common field will be the same.
Syntax:
The syntax for SQL INNER JOIN is:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
Here,
- table1: First table.
- table2: Second table
- matching_column: Column common to both the tables.
SELECT * FROM TableA INNER JOIN TableB ON TableA.name = TableB.name
id name id name
1 Pirate 2 Pirate
3 Ninja 4 Ninja
NOTE : Inner join produces only the set of records that match in both
Table A and Table B.
id name id games
-- ---- -- ----
1 Pirate* 1 Cricket
2 Monkey 2 Hockey
3 Ninja* 3 Chess
4 Spaghetti 4 Tenis
id name id games
-- ---- -- ----
1 Pirate* 1 Crickets
2 Monkey 2 Hockey
3 Ninja* 3 Chess
4 Spaghetti 4 Ninja*
A Left Join is like throwing a party where everyone is invited, and those with matching interests bring their plus-ones. It returns all rows from the left table and matching rows from the right table.
Syntax
The syntax of LEFT JOIN in SQL is:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
Here,
- table1: First table.
- table2: Second table
- matching_column: Column common to both the tables.
SELECT * FROM TableA LEFT OUTER JOIN TableB ON TableA.name = TableB.name
id name id name
-- ---- -- ----
1 Pirate 2 Pirate
2 Monkey null null
3 Ninja 4 Ninja
4 Spaghetti null null
The Right Join command returns all rows from the right table, and the matching records from the left table. The result is NULL from the left side, when there is no match.
The syntax of RIGHT JOIN in SQL is:
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
Order Table;
OrderID CuctomerID EmployeeID ShipperID
10308 2 7 3
10309 37 3 1
10304 77 8 2
Employee Table;
EmployeeId LastName FirstName Photo
1 Oax Sam photo1.jpeg
2 RamSam Lita photo2.jpeg
3 Mark Leee photo3.jpeg
SELECT * Orders RIGHT JOIN Customers Orders.CustomerID=Customers.CustomerID;
The above query will display all the records from the Customers table plus all the matches in
the Orders table.
FULL JOIN in SQL combines rows from both tables i.e. left and right table when there is a match in either of the tables.
FULL JOIN can be considered as the combination of LEFT JOIN and RIGHT JOIN.
When there is no match at a given position then NULL is displayed at that particular position.
Thank you;