Sql inner join syntax error

INNER JOIN Syntax Error

Part of Google Cloud Collective

I would like to JOIN 2 databases.

• 1 database is keyword_data (keyword mapping)
• 1 database is filled with Google rankings and other metrics

Somehow I cannot JOIN these two databases.

DATA SET NAME: visibility

TABLE 1

VALUES

DATA SET NAME: visibility

TABLE 2

VALUES

In order to receive ranking data by date I wrote the following SQL line.

(besides that, 100 other lines with no success 😉 )

I am using Google BigQuery for this with standard SQL (unchecked Legacy SQL).

How can I JOIN those 2 data tables?

2 Answers 2

How familiar are you with SQL? I think you’re using aliases wrong, something like this should work

First of all i have never worked with Google big query but there is a couple of things wrong in my opinion with this query.

To start with you join tables by including the name of the table then you provide the key that the tables are joined by. Also if you don’t use aggregate functions (MIN/MAX etc.) in your select statement you must include all values in the group by clause as well. In reference I can provide you a solution that would work if you would of used Microsoft SQL Server if that would be of any help because if you reference here the syntax is quite similar.

This question is in a collective: a subcommunity defined by tags with relevant content and experts.

Источник

Syntax error near JOIN in SQL Server database

The following SQL query is giving below error when run against a Microsoft SQL Server database from C#:

Incorrect syntax near the keyword JOIN

Certainly, the query contains the columns mentioned in the SELECT portion.

A very similar query is successfully parsed without any issues. I just can’t for the life of me see, why this isn’t working. For now, I would like someone to just make sure that there actually is a syntax error in this query as the system claims, because I sure can’t spot it.

There might also be an issue with the C# script that a «colleague» is using to run the query against the database, but that is a whole different issue.

2 Answers 2

I agree with tgolisch, getting table ALIAS name references both shortens the query from readability standpoint, but also in cases where you need the same table joined multiple times in the same query. Such as an employee ID and supervisor ID both point to a person table to get the name. Each ID represents a person. Having aliases can help.

Now, back to your original post. Does it actually work AS-WRITTEN under SSMS? when the patient ID is provided? If not, fix it there first. If the query DOES work, then its somewhere in how the query is prepared within your C# code. If so, post the entire code of C# (without actual login credential stuff), but the whole code to be assessed.

In its easiest test case, I would go in C# and try doing the query one step at a time such as getting EVERY column so you are not worrying about «CAST()» issues that may fail. ALSO, put a specific patient ID vs parameter just to test and make sure all the JOINs work. If you are failing on the join to begin with, it doesn’t matter WHAT the other fields are. If the parameter is bad, that too has been removed from the equation of problem. Only other alias I would add is for the «ScheduledActivity» table reference to ex: «SA» or «SchA», and «ActivityInstance» to «AI» or «ActI»

Update to show how I personally write my SQL queries in C# using the leading «@» which allows one string to span multiple lines. I get the entire clarity of the SQL in 1 statement without guessing at where the concatenation on multiple lines comes in. I even changed the parameter to show the SQL «@»-based parameter name. I also try to prefix with «@parm» to prevent any ambiguity of coming from some table, or parameter. no guessing.

Reformatted query to show indentation of where tables rely on previous. IMO, easier to follow how A->B->C are related, also updated the aliases as noted in top post.

Источник

SQL INNER JOIN syntax

the two bits of SQL below get the same result

I’ve seen both styles used as standard at different companies. From what I’ve seen, the 2nd one is what most people recommend online. Is there any real reason for this other than style? Does using an Inner Join sometimes have better performance?

I’ve noticed Ingres and Oracle developers tend to use the first style, whereas Microsoft SQL Server users have tended to use the second, but that might just be a coincidence.

Thanks for any insight, I’ve wondered about this for a while.

Edit: I’ve changed the title from ‘SQL Inner Join versus Cartesian Product’ as I was using the incorrect terminlogy. Thanks for all the responses so far.

7 Answers 7

Both queries are an inner joins and equivalent. The first is the older method of doing things, whereas the use of the JOIN syntax only became common after the introduction of the SQL-92 standard (I believe it’s in the older definitions, just wasn’t particularly widely used before then).

The use of the JOIN syntax is strongly preferred as it separates the join logic from the filtering logic in the WHERE clause. Whilst the JOIN syntax is really syntactic sugar for inner joins it’s strength lies with outer joins where the old * syntax can produce situations where it is impossible to unambiguously describe the join and the interpretation is implementation-dependent. The [LEFT | RIGHT] JOIN syntax avoids these pitfalls, and hence for consistency the use of the JOIN clause is preferable in all circumstances.

Note that neither of these two examples are Cartesian products. For that you’d use either

To answer part of your question, I think early bugs in the JOIN . ON syntax in Oracle discouraged Oracle users away from that syntax. I don’t think there are any particular problems now.

They are equivalent and should be parsed into the same internal representation for optimization.

Actually these examples are equivalent and neither is a cartesian product. A cartesian product is returned when you join two tables without specifying a join condition, such as in

There is a good discussion of this on Wikipedia.

Oracle was late in supporting the JOIN . ON (ANSI) syntax (not until Oracle 9), that’s why Oracle developers often don’t use it.

Personally, I prefer using ANSI syntax when it is logically clear that one table is driving the query and the others are lookup tables. When tables are «equal», I tend to use the cartesian syntax.

The performance should not differ at all.

The JOIN. ON. syntax is a more recent addition to ANSI and ISO specs for SQL. The JOIN. ON. syntax is generally preferred because it 1) moves the join criteria out of the WHERE clause making the WHERE clause just for filtering and 2) makes it more obvious if you are creating a dreaded Cartesian product since each JOIN must be accompanied by at least one ON clause. If all the join criteria are just ANDed in the WHERE clause, it’s not as obvious when one or more is missing.

Both queries are performing an inner join, just different syntax.

An INNER JOIN statement can be rewritten as a CROSS JOIN with a WHERE clause matching the same condition you used in the ON clause of the INNER JOIN query.

Table relationship

Considering we have the following post and post_comment tables:

The post has the following records:

and the post_comment has the following three rows:

SQL INNER JOIN

The SQL JOIN clause allows you to associate rows that belong to different tables. For instance, a CROSS JOIN will create a Cartesian Product containing all possible combinations of rows between the two joining tables.

While the CROSS JOIN is useful in certain scenarios, most of the time, you want to join tables based on a specific condition. And, that’s where INNER JOIN comes into play.

The SQL INNER JOIN allows us to filter the Cartesian Product of joining two tables based on a condition that is specified via the ON clause.

SQL INNER JOIN — ON «always true» condition

If you provide an «always true» condition, the INNER JOIN will not filter the joined records, and the result set will contain the Cartesian Product of the two joining tables.

For instance, if we execute the following SQL INNER JOIN query:

We will get all combinations of post and post_comment records:

So, if the ON clause condition is «always true», the INNER JOIN is simply equivalent to a CROSS JOIN query:

SQL INNER JOIN — ON «always false» condition

On the other hand, if the ON clause condition is «always false», then all the joined records are going to be filtered out and the result set will be empty.

So, if we execute the following SQL INNER JOIN query:

We won’t get any result back:

That’s because the query above is equivalent to the following CROSS JOIN query:

SQL INNER JOIN — ON clause using the Foreign Key and Primary Key columns

The most common ON clause condition is the one that matches the Foreign Key column in the child table with the Primary Key column in the parent table, as illustrated by the following query:

When executing the above SQL INNER JOIN query, we get the following result set:

So, only the records that match the ON clause condition are included in the query result set. In our case, the result set contains all the post along with their post_comment records. The post rows that have no associated post_comment are excluded since they can not satisfy the ON Clause condition.

Again, the above SQL INNER JOIN query is equivalent to the following CROSS JOIN query:

The non-struck rows are the ones that satisfy the WHERE clause, and only these records are going to be included in the result set. That’s the best way to visualize how the INNER JOIN clause works.

Not that this only applies to INNER JOIN, not for OUTER JOIN.

Источник

SQL INNER JOIN Keyword

SQL INNER JOIN Keyword

The INNER JOIN keyword selects records that have matching values in both tables.

INNER JOIN Syntax

Demo Database

In this tutorial we will use the well-known Northwind sample database.

Below is a selection from the «Orders» table:

OrderID	CustomerID	EmployeeID	OrderDate	ShipperID
10308	2	7	1996-09-18	3
10309	37	3	1996-09-19	1
10310	77	8	1996-09-20	2

And a selection from the «Customers» table:

CustomerID	CustomerName	ContactName	Address	City	PostalCode	Country
1

Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany 2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la ConstituciГіn 2222 MГ©xico D.F. 05021 Mexico 3 Antonio Moreno TaquerГ­a Antonio Moreno Mataderos 2312 MГ©xico D.F. 05023 Mexico

SQL INNER JOIN Example

The following SQL statement selects all orders with customer information:

Example

Note: The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are records in the «Orders» table that do not have matches in «Customers», these orders will not be shown!

JOIN Three Tables

The following SQL statement selects all orders with customer and shipper information:

Источник

SQLShack

A step-by-step walkthrough of SQL Inner Join

Organizations are generating and analyzing unmatched volumes of data with each passing minute. In this article, we will demonstrate how we can employ SQL Inner Join to query and access data from multiple tables that store this incessantly growing data in the SQL databases.

SQL Joins

Before we get started with SQL Inner Join, I would like to call out SQL Join here. Join is the widely-used clause in the SQL Server essentially to combine and retrieve data from two or more tables. In a real-world relational database, data is structured in a large number of tables and which is why, there is a constant need to join these multiple tables based on logical relationships between them. There are four basic types of Joins in SQL Server – Inner, Outer (left, right, full), Self and Cross join. To get a quick overview of all these joins, I would recommend going through this link, SQL Join types overview and tutorial.

This article targets all about the Inner Join in SQL Server, so let’s head over to it.

Definition of SQL Inner Join

Inner Join clause in SQL Server creates a new table (not physical) by combining rows that have matching values in two or more tables. This join is based on a logical relationship (or a common field) between the tables and is used to retrieve data that appears in both tables.

Assume, we have two tables, Table A and Table B, that we would like to join using SQL Inner Join. The result of this join will be a new result set that returns matching rows in both these tables. The intersection part in black below shows the data retrieved using Inner Join in SQL Server.

SQL Server Inner Join Syntax

Below is the basic syntax of Inner Join.

SELECT Column_list
FROM TABLE1
INNER JOIN TABLE2
ON Table1.ColName = Table2.ColName

Inner Join syntax basically compares rows of Table1 with Table2 to check if anything matches based on the condition provided in the ON clause. When the Join condition is met, it returns matched rows in both tables with the selected columns in the SELECT clause.

SQL Inner Join clause is the same as Join clause and works the same way if we don’t specify the type (INNER) while using the Join clause. In short, Inner Join is the default keyword for Join and both can be used interchangeably.

Note – We will use the keyword ‘Inner’ Join in this article for the sake of more clarity. You can omit it while writing your queries and can use only ‘Join’ as well.

SQL Inner Join in action

Let’s try to understand the concept of Inner Join through an interesting data sample that deals with a Pizza Company and its food distribution. I am going to create two tables first – table ‘PizzaCompany’ that manages different branches of Pizza outlets in a few cities and table ‘Foods’ that stores food distribution details across these companies. You can execute the code below to create and populate data into these two tables. All this data is hypothetical and you can create in any of your existing databases.

Источник