Joins In Sql

What is a Join in SQL Server?

Join is used  to put data from two or more tables into a single result set.

What are the types of Joins that we can have with Sql Server?

There are 4 types of joins: Inner Join, Outer Join, Cross Join , Self Join

INNER JOIN

Inner join shows matches only when they exist in both tables.Example in the below SQL there are two tables Customers and Orders and the inner join in made on Customers Customerid and Orders Customerid.So this SQL will only give you result with customers who have orders.

SELECT Customers.*, Orders.* FROM Customers INNER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

LEFT OUTER JOIN:

Left join will display all records in left table of the SQL statement whether their is a matching record in right table  or not. In SQL below customers with or without orders will be displayed. Order data for customers without orders appears as NULL values.

SELECT Customers.*, Orders.* FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

RIGHT OUTER JOIN:

Right join will display all records in right table of the SQL statement whether their is matching record in left table or not. In SQL below all orders with or without matching customer records will be displayed. Customer data for orders without customers appears as NULL values.

SELECT Customers.*, Orders.* FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID =Orders.CustomerID

CROSS JOIN:

It will return the cartesian product of rows of both the tables. Suppose their is 3 rows in first table and 5 in second able then it will return total of 15 rows.

SELECT Customers.*, Orders.* FROM Customers CROSS JOIN Orders ON Customers.CustomerID =Orders.CustomerID

                                                                  1  2  3

Faq's Interview Questions

What is a view?

If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It is derived from a table but has no storage space of its own and often may be used in the same manner as a table.

What is an Index?

Index is used primarily to speed up execution and impose UNIQUENESS upon data. When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.

What are the types of indexes available with SQL Server?

There are basically two types of indexes that we use with the SQL Server.

1. Clustered
2. Non-Clustered.

What is the basic difference between clustered and a non-clustered index?

Clustered index is unique for any given table and we can have only one clustered index on a table.  The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index.

Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can  have as many non-clustered indexes as we can on the db.

we can have 1 clustered index and 249 non clustered index per table.

What are cursors?
Cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table.

Difference between DELETE & TRUNCATE commands?

Delete command removes particular rows from a table that we provide with a WHERE clause. Truncate will actually remove all the rows from a table.
Delete command can be rolled back whereas Truncate command cannot be rolled back.

What command do we use to rename a db?

sp_renamedb ‘oldname’ , ‘newname’

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?

Having Clause is basically used only with the GROUP BY function in a query.
WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

What is BCP? When do we use it?

BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.

                                                                     1 2 3