Tag Archives: TSQL2sDay

T-SQL Tuesday #37: Invite To Join Me In a Month of Joins

TSQL2sdayThis month’s T-SQL Tuesday is being hosted by Sebastian Meine (blog | twitter) and his topic is anything and everything about “joins”. He’s dedicted the whole month of December to this topic.

Having had the opportunity to do extensive SQL work as a developer when first starting out I have been accustomed to joins for some time. Joins are one of the basic constructions of SQL and Databases as such – they combine records from two or more database tables into one row source. Depending on join type and join restrictions returned row count can be from 0 till all possible combinations of involved tables. Databases are built to make joins as efficient as possible. It means almost always joining data in database is more efficient than doing that somewhere else. It also means one has to know the power and possibilities of joins to fully exploit their strength.

Types of Joins

These are the most common joins that I have ran into:

  • INNER JOIN – only rows satisfying selection criteria from both joined tables are selected.
  • LEFT OUTER JOIN – rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from left joined table are being kept along with Nulls instead of actual right joined table values.
  • RIGHT OUTER JOIN – rows satisfying selection criteria from both joined tables are selected as well as all remaining rows from right joined table are being kept along with Nulls instead of actual left joined table values.
  • FULL OUTER JOIN – rows satisfying selection criteria from both joined tables are selected as well as all remaining rows both from left joined table and right joined table are being kept along with Nulls instead of values from other table.
  • CROSS JOIN – A cross join performs a cartesian product on the tuples of the two sets.

Usually cross joins are used quite rarely; some of the scenarios could be as follows:

  • Possibility to generate high amount of rows. As we can see from relatively small tables there is possibility to get quite monstrous numbers.
  • Find out all possible row combinations of some tables. Mostly this is useful for reports where one needs to generate all combinations for example all nationalities x genders for persons.
  • To join a table with just one row. Most often used to get some configuration parameters.

ALIAS JOINING

Some other questions I’ve been asked over the years is proper aliasing. I’ve had people use the a,b,c, method and a short table def. for example:

Example 1

Select *

from table 1 a (nolock)

inner join table 2 b on a.id = b.id

Example 2

Select *

from customer cust (nolock)

inner join address adr on cust.id = adr.id

The Performance

I’ve seen queries astronomicaly long with nasty subselects, joins, union alls, etc. Alot of times in my query tuning and looking at poorly designed queries, stored procedures, udf’s etc it is quickly seeable whether joins are being used correctly or incorrectly. When one takes the proper time to review the database and the schema and then build the queries with powerful joins performance can be quite optimal.

Future T-SQL Tuesdays

I urge you to take the time to check out Sebastians site and hey while I’m at it if you feel like you want to host a T-SQL Tuesday give Adam Machanic a shout. I can tell you from experience that I’m glad I did. Getting involved is a great way to get going with the SQL Community and the SQL family.

Thanks Sebastian for hosting this month!

The Roundup – #TSQL2sday

Another T-SQL Tuesday has come and gone and I’ve got to admit that all the responses that were received are pretty awesome and even had a few responses from first timers that attended the PASS Summit 2012. While all the posts were great, I had one that blew me away and is exactly what the Community is about. Kendal Van Dyke allowed a post to be tied to his blog by Andy Levy who currently didn’t have a blog and is fairly new to SQL. If you get a chance check it out.

One theme that I read over and over again is that we are one huge family and we have each others backs. The Community has a whole goes out of their way to assist our fellow members. Below is the roundup from everyone who chimed in. Thanks everyone for their participation!

Remember if you are interested in hosting please contact Adam Machanic (B|T)

POSTS

Jason BrimHall – SQL Family – After Summit 2012 | SQL RNNR

Rob Farley – Rob Farley : SQL Community – stronger than ever

Aaron Bertrand – T-SQL Tuesday : Reflections on the PASS Summit and our community

Robert Davis – T-SQL Tuesday #36 – SQL Community | SQLSoldier

Chris Shaw – Esprit de Corps T-SQL Tuesday #36 « Chris Shaw’s Weblog

Valentino Vranken – BI: Beer Intelligence? · PASS Summit 2012: Impressions Of A First Timer

Steve Jones – T-SQL Tuesday #36 – What Does Community Mean? « Voice of the DBA

Oliver Asmus – T-SQL Tuesday #36 – What Does the SQL Community Mean To You? | OliverAsmus.com

Edwin Sarmiento – PASS Summit and The Value Of Building A Community

Wayne Sheffield – T-SQL Tuesday #36 – Community

Mike Fal – T-SQL Tuesday #36 (#tsql2sday) – Coolness factor | Art of the DBA

Robert Pearl – TSQL Tuesday – What SQL Community Means To Me

Tamera Clark – SQL Community and Family

Kendal Van Dyke (On Behalf of Andy Levy) – T-SQL Tuesday #36 – SQL Community (Guest Post)

Kerry Tyler – T-SQL Tuesday #36: What Does the SQL Community Mean to You (Me)?

Argenis Fernandez – T-SQL Tuesday #36 (#tsql2sday)– Post-PASS Summit Depression

SQL Asylum – T-SQL Tuesday #36 SQL Community how you can get involved

Dev Jef: – T-SQL TuesDay #36 – What does the community mean to you? « SQL from the Trenches

Mickey Stuewe – T-SQL Tuesday #36 – What Community Means to a Newbie « Mickey’s T-SQL Ponderings

David Maxwell – T-SQL Tuesday #36: What Community Means To Me | David Maxwell on SQL Server

Stacia Misner – Data Inspirations » We are (SQL) Family! (T-SQL Tuesday #36)

Jenny Salvo – T-SQL Tuesday: What Does the SQL Community Mean to You? | Salvo(z)