Feed on
Posts
Comments

Archive for the ‘SQL Joins’ Category

SQL SERVER - Simulate INNER JOIN using LEFT JOIN statement - Performance Analysis

Posted in Author Pinal, SQL, SQL Authority, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 25, 2008 | 4 Comments »

Just a day ago, while I was working with JOINs I find one interesting observation, which has prompted me to create following example. Before we continue further let me make very clear that INNER JOIN should be used where it can not be used and simulating INNER JOIN using any other JOINs will degrade the [...]

Read Full Post »

SQL SERVER - Get Common Records From Two Tables Without Using Join

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 17, 2008 | 6 Comments »

I really enjoy answering questions which I receive from either comments or Email. My passion is shared by SQL Server Expert Imran Mohammed. He frequently SQL community members by answering their questions frequently and promptly.
Sachin Asked:
Following is my scenario,
Suppose Table 1 and Table 2 has same column e.g. Column1
Following is the query,
1. Select column1,column2 From [...]

Read Full Post »

SQL SERVER - Introduction and Example of UNION and UNION ALL

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 15, 2008 | 3 Comments »

It is very much interesting when I get request from blog reader to re-write my previous articles. I have received few request to rewrite my article SQL SERVER - Union vs. Union All - Which is better for performance? with examples. I request you to read my previous article first to understand what is the [...]

Read Full Post »

SQL SERVER - Guidelines and Coding Standards Complete List Download

Posted in Author Pinal, Best Practices, DBA, Data Warehousing, Database, SQL, SQL Authority, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL Data Storage, SQL Documentation, SQL Download, SQL Function, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, SQLAuthority, T SQL, Technology on September 25, 2008 | 3 Comments »

SQL SERVER - Guidelines and Coding Standards complete List Download
Coding standards and guidelines are very important for any developer on the path of successful career. A coding standard is a set of guidelines, rules and regulations on how to write code. Coding standards should be flexible enough or should take care of the situation where [...]

Read Full Post »

SQL SERVER - Guidelines and Coding Standards Part - 1

Posted in Author Pinal, Best Practices, Database, SQL, SQL Authority, SQL Coding Standards, SQL Constraint and Keys, SQL Documentation, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, Software Development, T SQL, Technology on September 23, 2008 | 13 Comments »

Use “Pascal” notation for SQL server Objects Like Tables, Views, Stored Procedures. Also tables and views should have ending “s”.
Example:
UserDetails
Emails

If you have big subset of table group than it makes sense to give prefix for this table group. Prefix should be separated by _.
Example:
Page_ UserDetails
Page_ Emails

Use following naming convention for Stored [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download

Posted in Author Pinal, Database, SQL, SQL Authority, SQL Constraint and Keys, SQL Cursor, SQL Data Storage, SQL DateTime, SQL Documentation, SQL Download, SQL Error Messages, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Security, SQL Server, SQL Server DBCC, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, SQL Utility, SQL XML, SQLAuthority, T SQL, Technology on September 20, 2008 | 12 Comments »

Download SQL Server 2008 Interview Questions and Answers Complete List
Interview is very important event for any person. A good interview leads to good career if candidate is willing to learn. I always enjoy interview questions and answers series. This is my very humble attempt to write SQL Server 2008 interview questions and answers. SQL Server [...]

Read Full Post »

SQL SERVER - 2008 - Interview Questions and Answers - Part 2

Posted in Author Pinal, Data Warehousing, SQL, SQL Authority, SQL Constraint and Keys, SQL Cursor, SQL Function, SQL Index, SQL Interview Questions and Answers, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQLAuthority, T SQL, Technology on September 13, 2008 | 9 Comments »

SQL SERVER - 2008 - Interview Questions and Answers Complete List Download
1) General Questions of SQL SERVER
What is Cursor?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time.
In [...]

Read Full Post »

SQL SERVER - 2008 - Introduction to Merge Statement - One Statement for INSERT, UPDATE, DELETE

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on August 28, 2008 | 3 Comments »

MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE, or DELETE data based on certain conditions, but now, using MERGE statement we can include the logic of such data modifications in one statement that [...]

Read Full Post »

SQL SERVER - EXCEPT Clause in SQL Server is Similar to MINUS Clause in Oracle

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on August 7, 2008 | 7 Comments »

One of the JR. Developer asked me a day ago, does SQL Server has similar operation like MINUS clause in Oracle.
Absolutely, EXCEPT clause in SQL Server is exactly similar to MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows in the first query that are not returned in the second query. [...]

Read Full Post »

SQL SERVER - 2005 - Difference Between INTERSECT and INNER JOIN - INTERSECT vs. INNER JOIN

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on August 3, 2008 | 27 Comments »

INTERSECT operator in SQL Server 2005 is used to retrieve the common records from both the left and the right query of the Intersect Operator. INTERSECT operator returns almost same results as INNER JOIN clause many times.
When using INTERSECT operator the number and the order of the columns must be the same in all queries [...]

Read Full Post »

SQL SERVER - Effect of Order of Join In Query

Posted in Author Pinal, SQL, SQL Authority, SQL Index, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on August 2, 2008 | 3 Comments »

Let us try to understand this subject with example.
We will use Adventurworks database for this purpose. Table which we will be using are HumanResources.Employee (290 rows), HumanResources.EmployeeDepartmentHistory (296 rows) and HumanResources.Department (16 rows).
We will be running following two queries and observe the output. In the resultset the order of first column (EmployeeID) is different in [...]

Read Full Post »

SQL SERVER - SQL SERVER - Simple Example of Recursive CTE - Part 2 - MAXRECURSION - Prevent CTE Infinite Loop

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on July 29, 2008 | 2 Comments »

Yesterday I wrote about SQL SERVER - SQL SERVER - Simple Example of Recursive CTE. I right away received email from regular reader John Mildred that if I can prevent infinite recursion of CTE.
Sure! recursion can be limited. Use the option of MAXRECURSION.
USE AdventureWorks
GO
WITH Emp_CTE AS (
SELECT EmployeeID, ContactID, LoginID, ManagerID, Title, BirthDate
FROM HumanResources.Employee
WHERE ManagerID [...]

Read Full Post »

SQL SERVER - Simple Example of Recursive CTE

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on July 28, 2008 | 6 Comments »

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression). You can read my previous articles about CTE by searching at http://search.SQLAuthority.com .
Here, the result of CTE is repeatedly used to get [...]

Read Full Post »

SQL SERVER - PIVOT and UNPIVOT Table Examples

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology, tagged , on June 7, 2008 | 12 Comments »

I previously wrote two articles about PIVOT and UNPIVOT tables. I really enjoyed writing about them as it was interesting concept. One of the Jr. DBA at my organization asked me following question.
“If we PIVOT any table and UNPIVOT that table do we get our original table?”
I really think this is good question. Answers is [...]

Read Full Post »

SQL SERVER - 2005 - Twelve Tips For Optimizing Sql Server 2005 Query Performance

Posted in Author Pinal, Best Practices, Database, SQL, SQL Authority, SQL Coding Standards, SQL Constraint and Keys, SQL Cursor, SQL Function, SQL Index, SQL Joins, SQL Performance, SQL Query, SQL Server, SQL Stored Procedure, SQL Tips and Tricks, SQL Trigger, T SQL, Technology on May 21, 2008 | 12 Comments »

I recently came across very nice article about optimization tips for SQL Server 2005. Here is the list of those 12 tips.
Twelve Tips For Optimizing Sql Server 2005 Query Performance
1. Turn on the execution plan, and statistics
2. Use Clustered Indexes
3. Use Indexed Views
4. Use Covering Indexes
5. Keep your clustered index small.
6. Avoid cursors
7. Archive old [...]

Read Full Post »

SQL SERVER - 2000 - SQL SERVER - Delete Duplicate Records - Rows - Readers Contribution

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 4, 2008 | 2 Comments »

I am proud on readers of this blog. One of the reader asked asked question on article SQL SERVER - Delete Duplicate Records - Rows and another reader followed up with nice quick answer. Let us read them both together.
Question from Mayank Mishra
Is it possible to delete a single column from a table in SQL [...]

Read Full Post »

SQL SERVER - Download FAQ Sheet - SQL Server in One Page

Posted in Author Pinal, Best Practices, DBA, Database, SQL, SQL Add-On, SQL Authority, SQL Constraint and Keys, SQL Documentation, SQL Download, SQL Function, SQL Index, SQL Joins, SQL Query, SQL Server, SQL Tips and Tricks, SQL Trigger, SQL Utility, Software Development, T SQL, Technology on April 28, 2008 | 26 Comments »

One of the most popular request I have received on this blog is to create one page which list all the SQL Server FAQs. SQL Server technology is very broad as well very deep. This is my humble attempt to list few of the daily used details in one page. Let me know your opinion [...]

Read Full Post »

SQL SERVER - Converting Subqueries to Joins

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on April 23, 2008 | 2 Comments »

There are always more than one way to do one thing in any programming languages. In SQL Server there are always more than one way to achieve same result set. It is quite often I see that developers write subqueries in place of joins or joins in place subqueries.
I recommend to read my previous article [...]

Read Full Post »

SQL SERVER - Better Performance - LEFT JOIN or NOT IN?

Posted in Author Pinal, SQL, SQL Authority, SQL Joins, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on April 22, 2008 | 14 Comments »

First of all answer this question : Which method of T-SQL is better for performance LEFT JOIN or NOT IN when writing query? Answer is : It depends! It all depends on what kind of data is and what kind query it is etc. In that case just for fun guess [...]

Read Full Post »

SQL SERVER - Simple Puzzle Using Union and Union All - Answer

Posted in Author Pinal, Database, SQL, SQL Authority, SQL Joins, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on March 25, 2008 | 3 Comments »

Yesterday I posted a puzzle SQL SERVER - Simple Puzzle Using Union and Union All, today we will see the answer of this. Following image explains the answer of puzzle.

You can read the explanation of why this is answer read my previous article SQL SERVER - Union vs. Union All - Which is better for [...]

Read Full Post »

Older Posts »