0

How do I get sqlalchemy to specify inner within its sql output for a join.

I'm using sqlalchemy to create views based on the below query. My scheme object just holds a number of related sqlalchemy Tables.

scheme = Core_MDGSQLP03.Staging_BasicGaTest()
type(scheme.Staging)
type(scheme.IdMatch)

scheme.IdMatch.c.ID.label('ID')
sql = sql.select(
    [scheme.IdMatch.c.ID.label('ID')] + \
    [c.label(c.name) for c in scheme.Staging.columns]
).\
select_from(
    scheme.IdMatch.join(
        scheme.Staging,
        scheme.Staging.c.DmHashKeys == scheme.IdMatch.c.DmHashKeys
    )
)
print(sql)

#output
#> <class 'sqlalchemy.sql.schema.Table'>
#> <class 'sqlalchemy.sql.schema.Table'>
#> 
SELECT [IdMatch].[BasicGaTest].[ID] AS [ID]
    , [Staging].[BasicGaTest].[SYS_OPERATION] AS [SYS_OPERATION]
    , [Staging].[BasicGaTest].[DmHashKeys] AS [DmHashKeys]
    , [Staging].[BasicGaTest].[ga:client] AS [ga:client]
    , [Staging].[BasicGaTest].[ga:source] AS [ga:source]
FROM [IdMatch].[BasicGaTest] 
JOIN [Staging].[BasicGaTest] 
ON [Staging].[BasicGaTest].[DmHashKeys] = [IdMatch].[BasicGaTest].[DmHashKeys]

Notice how my sql output reads JOIN. I really need that say INNER JOIN. How do I make SQLAlchemy specify this?

Jamie Marshall
  • 1,885
  • 3
  • 27
  • 50
  • As far as i know, `JOIN` is exactly semantically the same as `INNER JOIN` in all RDBMS. Do you really need this for some other reason? – van Mar 25 '20 at 14:20
  • @van it would be nice. These things tend to stay around for a long time and we ship SQL between lots of different sources in a rather large data operation. Assuming that 'JOIN' == 'INNER JOIN' will remain true for the next ten years wherever we ship our code seems a little dangerous to me. – Jamie Marshall Mar 25 '20 at 20:31
  • Please see https://stackoverflow.com/questions/565620/difference-between-join-and-inner-join and the links from the answers,where you can be pretty sure that this will not change in the next 10 years (_except for MS Access, which you most likely do not use anyways_). – van Mar 28 '20 at 17:27

0 Answers0