Skip to main content

How to Querying Data using Joins in Sql Programming

In a normalized database, the data to be viewed can be stored in multiple tables. When you need to view data from related tables together, you can query the data by joining the tables with the help of common attributes. You can also use subqueries where the result of a query is used as an input for the condition of another query.

This article discusses about how to query data from multiple tables by applying various types of joins, such as an inner join, outer join, cross join, equijoin, or self-join.

Querying Data by Using Joins

As a database developer, you may need to retrieve data from more than one table together as a part of a single result set. In such a case, different columns in the result set can obtain data from different tables. To retrieve data from multiple tables, the SQL Server allows you to apply joins. Joins allow you to view data from related tables in a single result set. You can join more than one table based on common attribute.

Depending on the requirements to view data from multiple tables, you can apply different types of joins listed below:

Inner Join

An inner join retrieves records from multiple tables by using a comparison operator on a common column. When an inner join is applied, only rows with values satisfying the join condition in the common column are displayed. Rows in both tables that do not satisfy the join condition are not displayed.

A join is implemented by using the SELECT statement, where the SELECT list contains the name of the columns to be retrieved from the tables. The FROM clause contains the names of the tables from which combined data is to be retrieved. The WHERE clause specifies the condition, with a comparison operator, based on which the tables will be joined.

The syntax of applying an inner join in the SELECT query is:

SELECT column_name, column_name [, column_name]
FROM table1_name JOIN table2_name
ON table1_name.ref_column_name join_operator
Table2_name.ref_column_name

Where
  • Table1_name and table2_name are the name of the tables that are joined join_operator is the comparison operator based on which the join is applied
  • Table1_name.ref_column_name and table2_name.ref_column_name are the names of the columns on which the join is applied.
Whenever a column is mentioned in a join condition, the column should be referred by prefixing it with the table name to which it belongs or with a table alias. A table alias is a name defined in the FROM clause of the SELECT statement to refer to the table with another name or to uniquely identify the table.

The following query displays the Employee ID and Title for each employee from the Emoloyee table and the Rate and PayFrequency columns from the EmployeePayHistory table

SELECT e.BusinessEntityID,e.JobTitle, eph.Rate, eph.PayFrequency
from HumanResources.Employee e
JOIN HumanResources.EmployeePayHistory eph
ON e.BusinessEntityID = eph.BusinessEntityID

In the preceding query, the Employee and EmployeePayHistory tables are joined on the common column, ID. The query also assigns e as the alias of the Employee table and eph as the alias of the EmployeePayHistory table. The column names are also listed with the table alias names.

How to Querying Data using Joins in Sql Programming

Based on the relationship between tables, you need to select the common column to set the join condition. In the preceding example, the common column is ID, which is the primary key of the Employee table and the foreign key of the EmployeePayHistory table.

Observe the result set after the join. The record of the employee with D as 1 from the Employee table is joined with the record of the employee with ID as 1 from the EmployeePayHistory table.

Comments

Popular posts from this blog

difference between structure and union in C Language

In c language article we will see the difference between union and structure. Both are the user define datatype in c language. See the table which is mentioned below: ASP.NET Video Tutorial Series Structure Union1.The keywordstruct is used to define a structure 1. The keyword union is used to define a union. 2. When a variable is associated with a structure, the compiler allocates the memory for each member. The size of structure is greater than or equal to the sum ofsizes of its members. The smaller members may end with unused slack bytes. 2. When a variable is associated with a union, thecompiler allocates thememory by considering the size of the largest memory. So, size of union is equal to the size of largest member. 3. Each member within a structure is assigned unique storage area of location. 3. Memory allocated is shared by individual members of union. 4. The address of each member will be in ascending order This indicates that memory for each member will start at different offset v…

Difference between Linear search and Binary Search in c language

SQL Video Channel : Download all SQL Video



Binary Search Linear Search Works only on sorted items. such as  1,2,3,4,5,6  etc
Works on sorted as well as unsorted items. 12,4,5,3,2,1 etc Very efficient if the items are sorted Very efficient if the items are less and present in the beginning of the list. such as Suppose your list items are : 12,3,4,5,1 and you want to search 12 number then you get beginning in the list. Works well with arrays and not on linked lists. Works with arrays and linked lists.
Number of comparisons are less More number of comparisons are required if the items are present in the later part of the array or its elements are more.

Memory representation of Linked List Data Structures in C Language

Memory representation of Linked List

             In memory the linked list is stored in scattered cells (locations).The memory for each node is allocated dynamically means as and when required. So the Linked List can increase as per the user wish and the size is not fixed, it can vary.

               Suppose first node of linked list is allocated with an address 1008. Its graphical representation looks like the figure shown below:


      Suppose next node is allocated at an address 506, so the list becomes,



  Suppose next node is allocated with an address with an address 10,s the list become,


The other way to represent the linked list is as shown below:




 In the above representation the data stored in the linked list is “INDIA”, the information part of each node contains one character. The external pointer root points to first node’s address 1005. The link part of the node containing information I contains 1007, the address of next node. The last node …