Home Forums SQL Server 2008 SQL Server Newbies Display two fields of two UNRELATED tables WITHOUT crossjoin RE: Display two fields of two UNRELATED tables WITHOUT crossjoin

  • dgowrij (2/5/2013)


    Hi Lynn,

    Just to understand, Could you please explain how the query works? Especially the part

    (SELECT 1) dt1(n)

    CROSS APPLY (SELECT COUNT(*) FROM dbo.Customers) dt2(q1)

    CROSS APPLY (SELECT COUNT(*) FROM dbo.Input) dt3(q2);

    What does (SELECT 1) dt1(n) means?

    And how it uses all these variables dt1, dt2, dt3, q1, q2 and n? Dont they need any declaration.

    Thanks in advance.

    Ah hah! You appear to be coming from a programming background. This now makes more sense. I might be able to help straighten a few things out for you.

    A SELECT statement is not an array that gets filled in afterwards, it's built during the data retrieval process. The reason you don't define those variables is they're scoped to that query only. dt1/2/3 are derived queries, and they are table name aliases. The syntax knows that from then on that those letters are aliases to queries. q1 and q2 are field name aliases, again, defined by the syntax of the subquery mechanism within SQL Server.

    They could as easily been written as (SELECT 1 AS n) AS dt1

    The reason for the starter query is cross apply runs once for each row from the previous set. A SELECT statement must always return a set of values (even if no rows), but you have to start it with something to work from. The dt1 there is the set that the cross applies hang off of.

    As I said, all queries start with looking towards a dataset. You can build an empty set with difficulty, but it's still a set to the query. All information in the query has to belong to the built set. JOINs, of all kinds, match set to set. When you take disparate information you're basically combining two arrays in some method. Either they're linked via data as the join you know or each is repeated for every component of the other, as in a cross join. To avoid that, and to basically put in pivoted data, you create a single row set to hang your other information off of.

    I'm not sure if that helped or hurt, but I'm hoping it will give you some insight.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA