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;

Right Join

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;